Off by 1 error with serial date some where?

[tsw]

Hi all,

Newbie here.. I'm seeing a weird off by 1 error working with dates functions and calculations.
While trying to prove a concept cube, I created a time dimension with startdate/enddate attributes using Performance Modeler.
Here's a sample values of a few months from the PM-generated timeline dimension:
Oct 2013 startdate(Numeric): 41548.208340 enddate(Numeric): 41578.208340
Nov 2013 startdate(Numeric): 41579.208340 enddate(Numeric): 41608.208340
Dec 2013 startdate(Numeric): 41609.208340 enddate(Numeric): 41639.208340

In a cube, I had store dates in a YYYY-MM-DD string, and use DAYNO() + 21916 (instead of 21915) to get it to be equivalent to the PM-generated timeline dimension attributes.
Doing so, when I needed to call DATE() function to recreate a date string from a serial date, I had to subtract 1 to get correct date string.

Has anyone else ran into this issue? is this a bug with the Performance Modeler generated serial date attributes? or an issue with the DATE() function ?

I'm testing on 10.1.1

[mattgoff]

See here. It's down a ways but you can search if you don't want to read it all (even though I highly recommend it).

Matt

[Duncan P]

This behaviour is specific to time dimensions created in Performance Modeller and is related to the time-zone in which the dimension is created, in that the start and end dates appear to be the UTC representations of integral dates in local time. You appear to be on the Eastern seaboard of North America. This means that the normal comparisons used to determine if a date is within a period don't work as expected. Which version are you seeing this in, 10.1 or 10.2?

[tsw]

Thanks, Matt. I read thru most of that, and that's how I built the date-related rules.

Duncan, I'm using 10.1.1
So you think the serial date attributes generated by PM are off by 1 day ? not sure what you are saying... help?

[Duncan P]

In your case they are off by 5 hours, which is I suspect your time-zone offset from UTC. If I were you I would write yourself a small TI process to replace each one with the nearest integer (which you can get with ROUND).

[tsw]

are you referring to the .208340 decimal in the attr?

I'm kind of taking that into account already...

If do a DAYNO('2013-10-01') I get 19632, but if I add 21915 => i get 41547
which is more than 1 day before the PM-generated attribute of 41548.208340

This forum thread talks about Excel having an extra day due to a bug... wondering if PM-generated attribute is has the same bug?

Thanks,

[Alan Kirk]

are you referring to the .208340 decimal in the attr?

I'm kind of taking that into account already...

If do a DAYNO('2013-10-01') I get 19632, but if I add 21915 => i get 41547
which is more than 1 day before the PM-generated attribute of 41548.208340

This forum thread talks about Excel having an extra day due to a bug... wondering if PM-generated attribute is has the same bug?

Thanks,

Assuming that Performance Muddler is intending to generate an attribute which gels with Excel's dates... you did read the part where I wrote that you need to add 21,916, not 21,915 to get to an Excel date value, right? 21915 is used when you're working with day of week values rather than serial dates. This wouldn't necessarily be a bug if the intent of Muddler is to give you the same datetime code as Excel uses. (And I can't say for sure one way or the other, as I haven't used that particular functionality, but from the above that's what it looks like.)

[tsw]

Thanks all,

For future readers of this thread:

Looks like PM's time population sets attributes using the Excel serial date numbers. And as Duncan P pointed out, there's some time zone fractions you might have to deal with.
But, most importantly, if you want to compare those attributes to serial dates obtained by calling DAYNO('yyyy-mm-dd'), you will have to add 21916.
If you want to call DATE(), to use MONTH(), or YEAR() functions, you will have to add 21915.

Hope this helps.

[Alan Kirk]

Thanks all,

For future readers of this thread:

Looks like PM's time population sets attributes using the Excel serial date numbers. And as Duncan P pointed out, there's some time zone fractions you might have to deal with.
But, most importantly, if you want to compare those attributes to serial dates obtained by calling DAYNO('yyyy-mm-dd'), you will have to add 21916.
If you want to call DATE(), to use MONTH(), or YEAR() functions, you will have to add 21915.

Hope this helps.

I'm afraid it won't, since it's incorrect and therefore potentially confusing. You get marks for intent, though.

As I wrote in the article, 21916 is the difference between an Excel date serial value, and a TM1 date serial value. This is always. Without exception. I am going to belabour that point, because it's important.

21915 is the difference that you use if you want to calculate the day of the week only because the DOW is out by 1 in Excel compared to its starting date of 1 Jan 1900.

DayNo() will return a value which is 21916 away from the corresponding Excel serial date. Always. Without exception.

Date() accepts a TM1 serial date and returns a date string. If you want it to match to Excel's serial date for the same date, the difference is 21916. Always, without exception. Proof?

Code: Select all

# Check the next line for yourself in Excel.
l_ExcelDate31Dec13 = 41639;
l_CalculatedTM1Date = 41639 - 21916;
s_CalculatedTM1Date = Date( l_CalculatedTM1Date,1);

AsciiOutput('F:\Temp\Dates.txt', s_CalculatedTM1Date);

Output?

Code: Select all

"2013-12-31"

As far as Month() and Year() are concerned, you aren't going to be adding or subtracting anything because they both accept string arguments and return a numeric value. The string is presumably going to be derived from Date() which, as demonstrated above, is 21916 distant from the corresponding Excel serial date.

However as Duncan alluded to way back at the top, if you are doing date only calculations then get rid of the decimal values in your attributes first. If you do some calculations with those in place then when the resulting value rounds to an integer (as it must at some point to derive a date) you may very well find that it rounds in a way that you hadn't considered fully, as a result of which you'll get the wrong serial number, as a result of which you'll get the wrong date.

[tsw]

Wow.. not sure how I got on your bad list.

I'm going to state the facts as I tested just now and list them here, and I'll let the intelligent readers determine if this helps them or not.

I'm using version 10.1.1

['A'] = N:DAYNO('2013-09-30') + 21916; # returns 41547
['B'] = N:ATTRN('Timeline Calendar Year', 'Sep 2013', 'End Date'); #this is Perf Modeler populated attribute and returns => 41547.21
['C'] = N:MONTH(DATE(DAYNO('2013-09-30') + 21916)); # returns 10 for Oct-01
['D'] = N:DAY(DATE(DAYNO('2013-09-30') + 21916)); # returns 1 for Oct-01
['E'] = N:MONTH(DATE(DAYNO('2013-09-30') + 21915)); # returns 9 for Sep-30
['F'] = N:DAY(DATE(DAYNO('2013-09-30') + 21915)); # returns 30 for Sep-30

[Alan Kirk]

Wow.. not sure how I got on your bad list.

I'm going to state the facts as I tested just now and list them here, and I'll let the intelligent readers determine if this helps them or not.

I'm using version 10.1.1

['A'] = N:DAYNO('2013-09-30') + 21916; # returns 41547
['B'] = N:ATTRN('Timeline Calendar Year', 'Sep 2013', 'End Date'); #this is Perf Modeler populated attribute and returns => 41547.21
['C'] = N:MONTH(DATE(DAYNO('2013-09-30') + 21916)); # returns 10 for Oct-01
['D'] = N:DAY(DATE(DAYNO('2013-09-30') + 21916)); # returns 1 for Oct-01
['E'] = N:MONTH(DATE(DAYNO('2013-09-30') + 21915)); # returns 9 for Sep-30
['F'] = N:DAY(DATE(DAYNO('2013-09-30') + 21915)); # returns 30 for Sep-30

You're not on my bad list; as I said you get marks for trying. But when people are new to something sometimes it pays for them to listen a little more, and be emphatic in their pronouncements a little less. Incorrect information for "the intelligent readers" Does. Not. Help. them.

Taking your Month example, it's worth asking yourself "returns 9 for Sep 30... of what?" Hint: I already told you in the earlier reply:

As far as Month() and Year() are concerned, you aren't going to be adding or subtracting anything because they both accept string arguments and return a numeric value.

Code: Select all

# Let's add 21915
l_Month_21915 = MONTH(DATE(DAYNO('2013-09-30') + 21915)); 
s_Month_21915 = Trim ( Str ( l_Month_21915, 2, 0 ) );

AsciiOutput( 'F:\Temp\Dates.txt', 'If we add 21915 what do we get? Ah, September.', s_Month_21915);

# Let's add 21916 instead.
l_Month_21916 = MONTH(DATE(DAYNO('2013-09-30') + 21916)); 
s_Month_21916 = Trim ( Str ( l_Month_21916, 2, 0 ) );

AsciiOutput( 'F:\Temp\Dates.txt', 'If we add 2191*6* what do we get? Oh noez, it is October! Could I have been wrong all this time?!.', s_Month_21916);

AsciiOutput( 'F:\Temp\Dates.txt', 'Oh, wait.. October of WHAT?');

AsciiOutput( 'F:\Temp\Dates.txt', DATE(DAYNO('2013-09-30') + 21916,1) );

AsciiOutput( 'F:\Temp\Dates.txt', 'Ah, October of the year 2073.');

Output:

Code: Select all

"If we add 21915 what do we get? Ah, September.","9"
"If we add 2191*6* what do we get? Oh noez, it is October! Could I have been wrong all this time?!.","10"
"Oh, wait.. October of WHAT?"
"2073-10-01"
"Ah, October of the year 2073."

Now do you see the problem? You're getting the right month for the wrong year.

When you are working with TM1 date functions, you use TM1 serial dates. You do not add back anything. You do not change them to Excel date serial codes, because then the functions will return the wrong value. You do not pass go, nor collect $200. You just use the TM1 serial date as it is. When you use the expression DAYNO('2013-09-30') then you are creating the serial date for 30 November 2013. You don't add roughly 60 years to it. You don't add anything to it. You have passed the string which represents that date already. The string has no connection whatsoever to the attribute values that Muddler has created for you. They're numeric, this is a string. They represent Excel serial dates, the DayNo() function represents a TM1 serial date. And when you work with functions that need the latter, you work with the latter.

Similarly the advice that you gave in this thread is going to have users passing dates which are roughly 60 years out from what they should be, which may occasionally return unexpected results. However since you've pointed them back to this thread there's no point me posting a follow-up there.

[tsw]

I see it now, thank you, Alan.

so the correct way to use the DATE() function in TM1 would be the following?

['A'] = N:DAYNO('2013-09-30') + 21916; # returns 41547 (use this to compare to PM's attribute after taking into acct timezone)
['B'] = N:ATTRN('Timeline Calendar Year', 'Sep 2013', 'End Date'); #this is Perf Modeler populated attribute and returns => 41547.21
['E'] = N:MONTH(DATE(DAYNO('2013-09-30'))); # returns 9 for Sep-30
['F'] = N:DAY(DATE(DAYNO('2013-09-30'))); # returns 30 for Sep-30

Thanks,

[Alan Kirk]

I see it now, thank you, Alan.

so the correct way to use the DATE() function in TM1 would be the following?

['A'] = N:DAYNO('2013-09-30') + 21916; # returns 41547 (use this to compare to PM's attribute after taking into acct timezone)
['B'] = N:ATTRN('Timeline Calendar Year', 'Sep 2013', 'End Date'); #this is Perf Modeler populated attribute and returns => 41547.21
['E'] = N:MONTH(DATE(DAYNO('2013-09-30'))); # returns 9 for Sep-30
['F'] = N:DAY(DATE(DAYNO('2013-09-30'))); # returns 30 for Sep-30

I think you've got the concepts of it down, but I'd add a couple of provisos.

The two serial date systems are almost like two languages; it's better to do the minimum amount of translation that you can. Consequently which conversions you use will depend on what you want to do with the values.
[A] I would only use if I needed to take a date that was generated within TM1 and compare it to the attributes, or in some way use it within Excel. If you need to use it for TM1 rules or TI, I'd stick with the native TM1 DayNo value.
is correct but as mentioned above if you're only working with the date component I'd recommend immediately taking the Int() value of that so that you don't run into rounding issues if you do date difference calculations. Also if you plan to use it in rules or TI exclusively I'd recommend then whacking 21916 off the value to convert it from an Excel serial date to a TM1 serial date.

For [E] and [F], yes, you have it exactly!