TI Process : Loading Dates

[Mark H]

Folks, frustrations galore.....


Want to load dates from a RDBMS into a TM1 staging cube. Then pass this to a planning cube for use in calculations.

When my target measure 'Estimated Launch Date' is defined as date format, the load fails.
When my target measure 'Estimated Launch Date' is defined as text format, the load works.

When i pass this text to the date field in the planning cube it fails on the 'cell type error'

When passing the date to planning cube it must be a true date as i need to reference it in conditional calcs - 'if started = no, then do ...'

What are the considerations here please ? Do i need to treat the date as numbers then convert ....?

Thanks

Mark

[qml]

TM1 only has two data types: numeric and string data. You will need to store your dates converted to one of these two types. I personally like to store data as string and with the correct order of Y/M/D you can even compare them, e.g. '2012-09-18' > '2012-09-17' (you can replace the dashes in your format with something else or remove them altogether). If you decide to store dates as numbers (e.g. 20120918 or the excel serial date format 40804) then comparison is just as easy.

[Mark H]

@qml

My conditional calc is roughly as follows.

Forecast period Start Date - setup as DATE format
Forecast period End Date - setup as DATE format

Product Active? = If 'Estimated Launch Date' falls between these two dates THEN "YES"

I cannot do this if usind differnet data types. If my 'Estimated Launch Date' is TEXT i cannot include it in my formula.

Ditto for start/end of month Buit-in-functions

Mark

[tomok]

Forecast period Start Date - setup as DATE format
Forecast period End Date - setup as DATE format

Format is just that...formatting for display purposes. It does not control the data type actually stored in TM1 or the type of comparisons you can do with the data. TM1 only stores two types of data, strings and floating point numeric. My personal preference is to store dates as numeric, just like you would see in Excel, and let the formatting option in TM1 (or Excel, depending on what client you are using) and control how it is displayed.

This post http://www.tm1forum.com/viewtopic.php?f=21&t=2441 will tell you all you need to know about dates.

[Mark H]

@tomok

Hadnt spotted that posting. Thanks

Will try to digest.

Mark

[Mark H]

Folks

I followed the instructions provided by Tomok and all works perfectly.

I have attached short document with screen prints to illustrate the instructions.

Thanks

Mark H

[girishramarao]

Thank You Mark. That document was of real help to my requirment. Thanks for putting it through.