Calc Manager Date Difference issue – Surprises in Calc Manager Part II


Well this one was an interesting surprise.

For those who are interested Vicks_ reported an issue in OTN forum, that the date difference calculation in Calc Manager are not showing up correctly.

For the dates given below

Start Date: 11/29/14
End Date: 03/09/15

It shows Number of days as 100 where it is really 101.

For those who are not aware of some of the date functions in Calc Manager, you can use the following functions to arrive at this result

@CalcMgrdatediff(“EndDate”,”Start date”,”Day”)
@HSPDateDiff(“EndDate”,”Start Date”)
@CalcMgrDaysBetween(“EndDate”,”Start Date”)

Now when I looked at the question I remembered about an issue with Octal numbers (I got burned by this when I was using a windows batch to update subvars)

I was at a client where the substitution variables were created from System date and I wrote a batch file to automate the process. There is one (among many, Quarters, Months,…I guess there were 21 subvars in total) sub var which gives the previous day.

SET DAY=08 set /a PRDAY=%DAY%-1 SET PRDD=0%PRDAY% SET PRDD=%PRDD:~-2% echo %DAY% %PRDAY% %PRDD%

invalid-octal-operation

octal-operation

What happens is when you add a 0 in front of a single digit number windows treats that as an octal number, however 08 and 09 are not valid Octal numbers. I was using unix date command to return date as


for /f "tokens=1,2,3,4,5,6* delims=," %%i in ('%BASE_DIR%Exesdate.exe +"%%Y,%%b,%%d,%%H,%%M,%%S,%%-m"') do set YR=%%i& set MTH=%%j& set DAY=%%k& set HR=%%l& set MIN=%%m& set SEC=%%n& set MONTHNUM=%%o

However after this I changed the day component to %%-d.
So first thing that came to my mind was since 9 was involved was this, however further testing revealed that was not the case.

The next thought was could it be a leap second issue, the way the day difference is calculated is convert the start and end date to milliseconds, find the difference and divide the difference with 86400000 (24*60*60*1000)

Now if you add a second then the result will not a whole number, however I found that this was not the case either. (Leap second addition is rare and the dates in question was not within that timeframe)

What else could be causing it, well if you said “Day Light Saving” you are absolutely correct!!!!

Now another reason to hate DST.

So let’s look at it.

dst-15

So anything after March 8 will have an issue and that why 100 instead of 101. The same will happen each year.
,br>

dst

Now the good thing was Calc Manager team released a quick patch (they changed the way the date difference were calculated)and the issue was confirmed to be solved. Didn’t I tell you that Calc Manager team is awesome….

Advertisements

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply