When working with Calculation scripts, sometimes a situation arises when we want to process dates, as in, compare date values, assign certain values on particular dates etc.
Now, I had a situation where I wanted to create a calendar for Fiscal year. I had standard
dimensions Period, Year and a custom dimension created named ‘Day’ which had members D01 through D31.
For each day I wanted to store what day of the week it would be. Also, for a entered date range in the format of YYYYMMDD, I had to assign ‘working’ or ‘nonworking’ flag.
Date entered through Planning is stored in YYYYMMDD format. Based on this format, I need to find out if it is a weekday/weekend or a ‘Holiday’ (Holiday range was specified by user in YYYYMMDD format).
The key to work (manipulate data) on the basis of dates is to find a unique identifier for the combination of Period, Year and Days member. That unique combination is the exact number of seconds elapsed since Jan 1, 1970 (also known as ‘epoch’)
The @TODATEEX() function returns the exact seconds elapsed since epoch. The issue is, this function requires strings to be passed, and we cannot pass numbers directly. Hence, we need to use @HspNumToString() function that converts number (eg.20141231 ). Again, the number needs to be split into three parts YYYY, MM and DD and passed separately to the function. The Essbase Techref will tell you how to use date
functions, but not how to parse numbers which are needed to use the date functions.
The following code that I had written shows how it is done:
/* varY is the date entered through Planning web form, the format of which is YYYYMMDD e.g. 20141231
SA0085 is the account member which will store the exact number of
seconds elapsed
The function works something like this when varY = 20141231
@TODATEEX("mm/dd/yyyy",’2014/31/12’) */
"SA0085"=@TODATEEX("mm/dd/yyyy",@CONCATENATE(@SUBSTRING(@HspNumToString(varY),4,6),@CONCATENATE("/", @CONCATENATE(@SUBSTRING(@HspNumToString(varY),6,8),@CONCATENATE("/", @SUBSTRING(@HspNumToString(varY),0,4))))));
NOTE: This function will fail at runtime if you pass invalid dates like 31 February or #MISSINGvalues.
Also NOTE: This function will work only till year 2037. Hopefully, we will have something else by that time.
Once we have the seconds elapsed value, other date functions like @DATEPART, @DATEDIFF can be used directly.
Cheers !
