Why these dates functions are not working for my Planning Application
Few weeks back I got a call from a friend of mine, saying "Rahul, Planning date function are giving weird results, they are resulting in some numbers which are not even remotely related to a date".
It can happen to anyone of us, if we are using date functions for the first time. Say if we have entered date like 20140101 (01-Jan-2014) and we need to add 15 days to this date to get a new date. A typical approach will be:
"NewDate" = @DATEROLL("OldDate", DP_DAY, 15);
You will expect a result as 20140116 but you will be amazed to see the results.
"NewDate" = 21436101 ?? ... yeah ??? this is some kind of encryption and will give proper results for only legitimate calc script writers
I know today is 20130619, let use @today function and see if we are getting this date:
"NewDate" = @TODAY();
Results which you will see: 1371600000
Now lets me tell you why part, Essbase Date function works on the basis of Gregorian Calender. Here date is a number representing the date between January 1, 1970 and Dec 31, 2037. The number is the number of seconds elapsed since midnight, January 1, 1970. To retrieve this number, use either of the following functions: @TODAY, @TODATEEX.
Above we got 1371600000 Seconds:
* I have considered 30 days per month so above seconds represents June 2013, if we start counting from 1st January 1970 (1970+43 = 2013).
In order to make these dates functions work we need to convert our YYYYMMDD format to XXXXXXXXXX Seconds format perform the operations using dates functions and convert it back to YYYYMMDD format.
Lets see one example: On the Basis of StartDate and NumberOfDays we need to calculate EndDate. If StartDate is 20140101 and NumberOfDays=100 then EndDate should be: 20140411
Enough to start with, let me know in case of questions.
It can happen to anyone of us, if we are using date functions for the first time. Say if we have entered date like 20140101 (01-Jan-2014) and we need to add 15 days to this date to get a new date. A typical approach will be:
"NewDate" = @DATEROLL("OldDate", DP_DAY, 15);
You will expect a result as 20140116 but you will be amazed to see the results.
"NewDate" = 21436101 ?? ... yeah ??? this is some kind of encryption and will give proper results for only legitimate calc script writers
I know today is 20130619, let use @today function and see if we are getting this date:
"NewDate" = @TODAY();
Results which you will see: 1371600000
Now lets me tell you why part, Essbase Date function works on the basis of Gregorian Calender. Here date is a number representing the date between January 1, 1970 and Dec 31, 2037. The number is the number of seconds elapsed since midnight, January 1, 1970. To retrieve this number, use either of the following functions: @TODAY, @TODATEEX.
Above we got 1371600000 Seconds:
1371600000 Seconds
22860000 Minutes
381000 Hours
15875 Days
43 Years
6 Months
* I have considered 30 days per month so above seconds represents June 2013, if we start counting from 1st January 1970 (1970+43 = 2013).
In order to make these dates functions work we need to convert our YYYYMMDD format to XXXXXXXXXX Seconds format perform the operations using dates functions and convert it back to YYYYMMDD format.
Lets see one example: On the Basis of StartDate and NumberOfDays we need to calculate EndDate. If StartDate is 20140101 and NumberOfDays=100 then EndDate should be: 20140411
VAR VarYear1, VarMonth1, VarDate1, TempStartDate, VarNoOfDays, TempEndDate, VarYear2, VarMonth2, VarDate2;
"BegBalance"
(
VarYear1 = @ROUND("StartDate"/10000,0);
VarMonth1 = @ROUND(("StartDate"-VarYear1*10000)/100,0);
VarDate1 = "StartDate"-(VarYear1*10000+VarMonth1*100);
TempStartDate = @TODATEEX ("yyyy-mm-dd", @JconcatStrings(@LIST( @JgetStringFromDouble (VarYear1,@_false,@_false),"-", @JgetStringFromDouble (VarMonth1,@_false,@_false),"-", @JgetStringFromDouble (VarDate1,@_false,@_false) ))) ;
VarNoOfDays = "NumberOfDays";
TempEndDate = @DATEROLL(TempStartDate, DP_DAY, VarNoOfDays);
VarYear2 = @DATEPART(TempEndDate,DP_YEAR);
VarMonth2 = @DATEPART(TempEndDate,DP_MONTH);
VarDate2 =@DATEPART(TempEndDate,DP_DAY);
"EndDate" = VarYear2 *10000+ VarMonth2*100+VarDate2;
)
ENDFIX
Cheers...!!!
Rahul S.
Thanks, this post save my life..
ReplyDeleteMarcio Pataca
It's also worth mentioning that the date may not display correctly in Planning if the evaluation order is not correct.
ReplyDelete