TM1 Forum

Posted: **Thu Jun 18, 2020 7:33 pm**

Could you believe it I think I've found a Millennium bug 20 years late. I have the following rule:

[{'Forecast Live','Final Capital Plan','Workplan-Latest','Capital Plan Workspace','Capital Plan Snapshot'},{'Stage 1','Stage 2','Stage 3','Stage 4','Stage 5'},{'Start Date','End Date'},'String'] = S:

  IF(DB('Capital - Reporting',!Capital - Reporting Version,!Capital - Projects,!Capital - Reporting Cost Category and Attributes,!Capital - Reporting Time,'Numeric') = 0

       ,''

       ,IF(TimVl((DB('Capital - Reporting',!Capital - Reporting Version,!Capital - Projects,!Capital - Reporting Cost Category and Attributes,!Capital - Reporting Time,'Numeric')+7095),'M')<10

             ,'0'|STR(TimVl((DB('Capital - Reporting',!Capital - Reporting Version,!Capital - Projects,!Capital - Reporting Cost Category and Attributes,!Capital - Reporting Time,'Numeric')+7095),'M'),1,0)

             ,STR(TimVl((DB('Capital - Reporting',!Capital - Reporting Version,!Capital - Projects,!Capital - Reporting Cost Category and Attributes,!Capital - Reporting Time,'Numeric')+7095),'M'),2,0)

            )|'/'|

        IF(TimVl((DB('Capital - Reporting',!Capital - Reporting Version,!Capital - Projects,!Capital - Reporting Cost Category and Attributes,!Capital - Reporting Time,'Numeric')+7095),'D')<10

             ,'0'|STR(TimVl((DB('Capital - Reporting',!Capital - Reporting Version,!Capital - Projects,!Capital - Reporting Cost Category and Attributes,!Capital - Reporting Time,'Numeric')+7095),'D'),1,0)

             ,STR(TimVl((DB('Capital - Reporting',!Capital - Reporting Version,!Capital - Projects,!Capital - Reporting Cost Category and Attributes,!Capital - Reporting Time,'Numeric')+7095),'D'),2,0)

           )|'/'|

        STR(TimVl((DB('Capital - Reporting',!Capital - Reporting Version,!Capital - Projects,!Capital - Reporting Cost Category and Attributes,!Capital - Reporting Time,'Numeric')+7095),'Y'),4,0)

      )

It works fine for any date after 2000 but not for any after. We're using the rule to convert a date number in to a string btw.

: Y2K Not Jericho.PNG (31.5 KiB) Viewed 5520 times

Posted: **Thu Jun 18, 2020 8:30 pm**

Hi Jim

I am not sure that I understand the point you are making and I haven't read your rules but I am wondering if the issue is because of an anomaly between Excel's base date and TM1's base date for date values?

# Adjust using Max and Min so we can rely on what goes into Date 1 YYYY-MM-DD being the higher of Date 1 and 2
# and for Date 2 YYYY-MM-DD being the lower of Date 1 and 2

# The fiddle factor of 21916 just seems to be necessary due differences in dates based on a start of 1900 or 1960.

['Date 1 YYYY-MM-DD']=S: DATE( Max( ['Date 1'] , ['Date 2'] ) - 21916 , 1 ) ;
['Date 2 YYYY-MM-DD']=S: DATE( Min( ['Date 1'] , ['Date 2'] ) - 21916, 1 ) ;

Regards

Paul

Posted: **Thu Jun 18, 2020 8:51 pm**

Paul the issue is that anything below the year 2000 is not being converted from a numeric to a string correctly so if you look at the image you can see highlighted it's converting 11/01/1991 in to 04/06/1971, yet for 06/30/2008 it converts it correctly. I've done this with both TimSt and TimVl. I get the same result.

I don't understand what you mean by date 1 and date 2? I'm just doing a straight conversion of a date from numeric to string?

Posted: **Thu Jun 18, 2020 8:59 pm**

Hi Jim

That was just an example - the important thing is the conversion factor. Date serial numbers in Excel start at 1900, and in TM1 they start at 1960. That conversion factor of - 21916 is the number of days different between 1st Jan 1900 and 1st Jan 1960. I suspect that you might be hitting a problem due to that.

Regards

Paul

Posted: **Thu Jun 18, 2020 9:05 pm**

paulsimon wrote: ↑Thu Jun 18, 2020 8:59 pm Hi Jim

That was just an example - the important thing is the conversion factor. Date serial numbers in Excel start at 1900, and in TM1 they start at 1960. That conversion factor of - 21916 is the number of days different between 1st Jan 1900 and 1st Jan 1960. I suspect that you might be hitting a problem due to that.

Regards

Paul

But then the issue I'm getting would impact all dates rather than just those before 2000?

Posted: **Thu Jun 18, 2020 9:09 pm**

I just swapped over from adding 7095 to subtracting 21916 and I get the exact same result. Any date before 2000 does not convert properly. By the way I've checked this in both 10.2.2 and PA,

Jim.

Posted: **Fri Jun 19, 2020 1:28 am**

Using Formatting in a View assumes any number being displayed as a date is an Excel Serial Date number. TimVl assumes any number being passed to it is a TM1 Serial Date number.

In PA you can set UseExcelSerialDate in the config file to make all of TM1 use Excel Serial Dates, but that's not something to be turning on in an existing system without a full review of all the date functionality.

Assuming you don't have that parameter set, and if the view format is showing you the number you want, go with the -21916, but also turn on the Extended Years flag on your TimVl functions. Without this, adding 7095 is taking you outside the range of the function.

Posted: **Fri Jun 19, 2020 3:48 pm**

Thanks Andy. I'll give that a try and see how it goes. I'll report back what happens when I add the parameter. I did move to converting using 21916 and still got the same problem.

Posted: **Fri Jun 19, 2020 9:14 pm**

Guys,

I can confirm that adding the parameter fixes the problem. Thanks Andy,

Jim.

TM1 Forum

Millennium Bug

Millennium Bug

Re: Millennium Bug

Re: Millennium Bug

Re: Millennium Bug

Re: Millennium Bug

Re: Millennium Bug

Re: Millennium Bug

Re: Millennium Bug

Re: Millennium Bug