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:
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

Enough to start with, let me know in case of questions.

Cheers...!!!
Rahul S.

Comments

  1. AnonymousApril 15, 2014 at 12:37 PM

    Thanks, this post save my life..

    Marcio Pataca

    ReplyDelete
  2. AnonymousNovember 29, 2017 at 2:07 PM

    It's also worth mentioning that the date may not display correctly in Planning if the evaluation order is not correct.

    ReplyDelete

Post a Comment

Popular posts from this blog

Multiple Navigation Flows are Active

Image
We have witnessed an issue where application migration or sometimes service restart in PBCS/EPBCS activates multiple navigation flows.  If multiple navigation flows are active you wont be able make changes in any of them, because in order to make change in navigation flow you have to make it inactive. Typically you may end up in following situation:  Solution is pretty simple: 1. Create a copy of any of the existing navigation flow:  2. Give it any desired name:  3. Now, you will see one additional navigation flow, with inactive status: 4.  Make it active: 5.  At this point of time you will have one active navigation flow and two inactive  6. Select the additional navigation flow you have created and delete it  7. Now you are back in business: Rahul S.
Read more

The Member Does Not Exists For The Specified Cube

Image
The member "" does not exist for the specified cube or you do not have access to it. Issue:  If you are running into a issue, where for a RTP member selection is throwing an error for a missing member, this regardless of the member you have selected in RTP or regardless of rule. You may be able to execute the rule from Calc Manager and not from Planning. Reason: You may run into this issue if: 1. You have added members in Planning and have not refreshed the cube 2. For the default value of a RTP in calc member you have a member which is not valid any more for planning application 3. For the default value of a RTP in calc member you have a member which is not valid any more for a plan type Solution: 1. Refresh cube 2. Update Default value of RTP which is causing issue to member which exists 3. Deploy the rule Rahul S.
Read more

"Smart View handled an unknown exception thrown by Microsoft Office" Error on Vista, Windows 7, Windows 2008

My MS-Office Components are Crashing After Installing Smart View on Vista, Windows 7, Windows 2008! This is one more one pretty common issue,   "Smart View handled an unknown exception thrown by Microsoft Office."   This is actually a known issue in version 11.1.2.1. If you look at the read me of Smart View you will find bug# 11696583 "On Vista, Windows 2008, and Windows 7 operating systems, Excel may terminate abruptly upon launching after you install Smart View. Workaround: Turn off “User Access Control” in User Accounts, available from the Control Panel." Here is the link to read me: http://download.oracle.com/docs/cd/E17236_01/epm.1112/readme/sv_1112100_readme.html When the User Access Controls are on Smart View faces problem in registering, when you set it off Smart View register itself like a charm. To have more information on UAC: http://technet.microsoft.com/en-us/library/cc709691(WS.10).aspx Now, when y
Read more