Millennium Bug

Post Reply
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Millennium Bug

Post by jim wood »

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

Code: Select all

[{'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
Y2K Not Jericho.PNG (31.5 KiB) Viewed 3729 times
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Millennium Bug

Post by paulsimon »

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
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Millennium Bug

Post by jim wood »

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?
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Millennium Bug

Post by paulsimon »

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
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Millennium Bug

Post by jim wood »

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?
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Millennium Bug

Post by jim wood »

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.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Andy Key
MVP
Posts: 351
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1
Version: 2.5 to PA
Excel Version: Lots
Location: Sydney
Contact:

Re: Millennium Bug

Post by Andy Key »

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.
Andy Key
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Millennium Bug

Post by jim wood »

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.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Millennium Bug

Post by jim wood »

Guys,

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

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Post Reply