TM1excel format for dates 1/1/1900 for empty (zero) items

[BigG]

hi all, I know there is a heap of information on dates, but cant seem to figure out the approach for my date in Excel cube view when no date in cell. Its the equivalent to entering a zero, giving you 1/1/1900 as the result. I cannot seem to prevent this, is there a solution here. See screenshots below

exce.png (3.49 KiB) Viewed 4027 times

tm1.png (8.77 KiB) Viewed 4027 times

[tomok]

Use this formula in cell:
=IF(DBRW(Cube,Dim1,Dim2...)=0,"",DBRW(Cube,Dim1mDim2.....))

[BigG]

magic! cheers

[lotsaram]

Use this formula in cell:
=IF(DBRW(Cube,Dim1,Dim2...)=0,"",DBRW(Cube,Dim1mDim2.....))

Actually you would be much better off just using a custom number format to hide null values as the approach of wrapping an IF statement around a DBRW means TM1 cannot optimally batch the DBRW array and it effectively becomes like a DBR. If the spreadsheet is large this could affect performance.

Just go to number formatting, select custom and enter "dd/mm/yyyy;;" (or mm/dd/yyyy;; for folks in North America)

This will hide all dates that evaluate to "zero" without need to change any formulas.

[BigG]

even more magical. cheers

[BigG]

seems like the formatting works in excel but is not liked in web'

2011-02-08_0853.png (3.43 KiB) Viewed 3971 times

[lotsaram]

Didn't realise you wanted to put the worksheet on TM1 web. The support for custom number formats (especially date formats) on TM1 web leaves a lot to be desired. In a websheet I think tomok's suggestion might be the only workable solution.

However you can use custom number formatting to hide null numeric values in TM1 web cube views (or contributor) by editing the Format attribute in your measure dimension. Possibly this might be an option for you if you want to use cube views directly.