Page 1 of 1
TM1excel format for dates 1/1/1900 for empty (zero) items
Posted: Fri Feb 04, 2011 4:03 am
by 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 4575 times
- tm1.png (8.77 KiB) Viewed 4575 times
Re: TM1excel format for dates 1/1/1900 for empty (zero) item
Posted: Fri Feb 04, 2011 4:21 am
by tomok
Use this formula in cell:
=IF(DBRW(Cube,Dim1,Dim2...)=0,"",DBRW(Cube,Dim1mDim2.....))
Re: TM1excel format for dates 1/1/1900 for empty (zero) item
Posted: Fri Feb 04, 2011 4:50 am
by BigG
magic! cheers
Re: TM1excel format for dates 1/1/1900 for empty (zero) item
Posted: Fri Feb 04, 2011 5:28 am
by lotsaram
tomok wrote: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.
Re: TM1excel format for dates 1/1/1900 for empty (zero) item
Posted: Mon Feb 07, 2011 1:03 am
by BigG
even more magical. cheers
Re: TM1excel format for dates 1/1/1900 for empty (zero) item
Posted: Mon Feb 07, 2011 10:56 pm
by BigG
seems like the formatting works in excel but is not liked in web'
- 2011-02-08_0853.png (3.43 KiB) Viewed 4519 times
Re: TM1excel format for dates 1/1/1900 for empty (zero) item
Posted: Tue Feb 08, 2011 3:14 am
by 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.