Date format in Asciioutput

[telula]

Hello,
I am trying to export the the start and end date of a contract using T1 function. The csv file gives a serial number for the date.
When I tried to modify the TI using
startdate=DATE(V3,1);
ASCIIOUTPUT(\\folder\contract.csv',V1,V2,startdate);
its gives the wrong year. the result is 66-01-02 when the date is supposed to be 01/01/2006
Can anyone tell me how to fix this....

[Alan Kirk]

Hello,
I am trying to export the the start and end date of a contract using T1 function. The csv file gives a serial number for the date.
When I tried to modify the TI using
startdate=DATE(V3,1);
ASCIIOUTPUT(\\folder\contract.csv',V1,V2,startdate);
its gives the wrong year. the result is 66-01-02 when the date is supposed to be 01/01/2006
Can anyone tell me how to fix this....

You presumably read the help file entry on the Date function, so is V3 in fact a serial number? Is it a serial number in TM1's format, that is, starting from 1 January 1960?

[telula]

Alan,
The date in the cube, is in dd/mm/yyyy format.When we use the cube view from that cube as a datasource, in the TI process Preview section, V3 is showing as a serial number.

[Alan Kirk]

Alan,
The date in the cube, is in dd/mm/yyyy format.When we use the cube view from that cube as a datasource, in the TI process Preview section, V3 is showing as a serial number.

I'd ask what the actual serial number is, but I can already guess what the problem is.

Settle back everyone, this is going to take a while. I'm thinking that it'll also need to go into the bug forum, but I haven't read through the encyclopaedia of New! Support! Procedures! from IBM, so I'm not in a position to submit it at this time.

Dates in Excel are merely serial numbers. The number 1 represents 1 January 1900. 1 January 2006 would be represented by the value 38718.

However TM1 uses a different system. As per the TimVl and TimSt help file entries,

The integer part of the number specifies the date, and the decimal part specifies the time within the day. Day number 0 corresponds to '60-01-01.' Negative numbers correspond to prior years. Years in the 21st Century, up to 2059, are represented by years 00 through 59. An hour is 1/24th of a day, a minute 1/60th of an hour, and a second 1/60th of a minute.

This is from the 8.2.12 version which doesn't support extended years, but the key point in the 9.4 help file is the same; that is, 0 is 1 January 1960, 1 is 2 January 1960 and so on.

That means that 0 in TM1's calculation method is the same as 21916 in Excel's method.

The problem is that when you format a dimension to display its values in date format, the Cube Viewer shows the number as it would be based on Excel's, date system, not TM1's. However whenever TM1 rules functions (like Date()) do calculations with the value, they use TM1's date system, not Excel's.

For those wishing to reproduce this, try this test:
- Create a dummy cube to hold a numeric value.
- Enter the value 21916 into that cube.
- Edit the element attributes for one of the dimensions, and set it to one of the Date functions.
- Recalculate the cube view.
You should see 1 January 1960.

Save that as a view, and create a TI to export that value using the Date() function.

Code: Select all

AsciiOutput('C:\Temp\Date1.txt', Date(Value,1));

(Note that I was able to use the "1" argument because I did this test in 9.4 rather than 8.2.12.)

The result that you'll get is 2020-01-02.

Conversely, if you add a statement to print out DayNo('20-01-02') (after converting it to a string, obviously), the result that you'll get is 21916.

In your case, I'll bet that the serial number that you're seeing is 38718.

If you format a cube to display that value as a date, it will show up as 1 January 2006 since that's day 38718 in the Excel date number series. However if you apply a Rules function to it, it will be regarded as 2 January 2066, since that is day 38718 in the TM1 series. (I'm not clear why you were getting only a 2 digit year given that you were using the extended year argument, unless you're on a version which doesn't support it.)

In a nutshell, yes, I'd regard this as a bug. If you want the values to be displayed in date format, the only option I can see is for you to keep the values as they are but subtract 21916 from the values when you apply a rules functions to them in TI. (This is only necessary when you want formatted date outputs; otherwise it won't matter.) Just be aware that you'll need to change that code if this bug gets fixed. However how Iboglix will fix this bug without causing a world of hurt to people who use values formatted as date, I'm not quite sure.

[Steve Rowe]

I tend to have my date elements set-up to be strings like "D 2009-09-28" then in the background I have a control cube that has the TM1 and Excel serials that I can use for reference in rules as alias.

Obviously just a workaround