how to dynamically show dates on a day dimension

In “tips and tricks” by Arthur van den Berg

Enrich your Day dimension with neat dates, month numbers and day numbers, so the planners can actually feel if they are planning on dates. Copy and paste these dynamic formulas in your outline.

Arthur van den Berg

After spending a couple of hour finding the right syntax and construct I finally found the right formula to do this neat trick. It is rather specific, but if it happens that you need to work with a planning/ Essbase model that has a Day dimension, this is just your “tip of the day”.

This is the database set-up:

  • Fiscal year -runs from Feb to Jan
  • Year dimension – standard
  • Day dimension – with a member for each day in the year (Day 32 – Day 397, meaning 1 Feb this year till 31 Jan next year)

Now, I wanted to show my users for each combination of Year and Day the

  • CalendarDate
  • Month Nr
  • Day in the month

 

exactly as in the following form:

dynamically generated date related values

And this is how you achieve this:

  1. add three dynamic calc members to the Account dimension: “CalendarDate”, “MonthNr” and “Day in the Month”;
  2. set “CalendarDate” to the data type “Date”, the others to “Non-Currency”;
  3. use the following formulas for these accounts:

CalendarDate:

show dates as dynamic formula in PBCS

Essbase formula to automatically calculate the date based on the day and year dimension

Looks pretty complex, huh?

The surrounding IF … END IFs ensure the formula is only calculated for level 0 Days and level 0 Years. The trick is in copying the year number form the Year dimension, using @SUBSTRING(@CURRMBR(“Years”),2,4) and adding Jan 1st to it. The @TODATEEX formula translates it to an internal number that represents the number of seconds since Jan 1st, 1970.

Then, the @DATEROLL adds the number of days, which come from the Day dimension. And finally the result is formatted as “yymmdd” (too bad there is no “yyyymmdd” format available). This string is converted to a number and to get the numeric date the value 20,000,000 is added. So date Apr 3rd, 2017 will become 20170403.

The formulas for “MonthNr” and “Day in the Month” are a variation to this formula:

Day in the Month:

IF (@ISMBR(@RELATIVE("Years",0)))

/* Only apply the formula on real years */

IF (@ISMBR(@RELATIVE("Total Year",0)))

@DATEPART(

@DATEROLL(@TODATEEX("yymmdd",@CONCATENATE(@SUBSTRING(@NAME(@CURRMBR("Years")),2,4),"0101") )

,DP_DAY

,@CalcMgrDoubleFromString(@SUBSTRING(@NAME(@CURRMBR("Day")),4))-1)

, DP_DAY)
;

ENDIF

ENDIF

 

MonthNr:

IF (@ISMBR(@RELATIVE("Years",0)))

/* Only apply the formula on real years */

IF (@ISMBR(@RELATIVE("Total Year",0)))

@DATEPART(

@DATEROLL(@TODATEEX("yymmdd",@CONCATENATE(@SUBSTRING(@NAME(@CURRMBR("Years")),2,4),"0101") )

,DP_DAY

,@CalcMgrDoubleFromString(@SUBSTRING(@NAME(@CURRMBR("Day")),4))-1)

, DP_MONTH)
;

ENDIF

ENDIF

As I said in the introduction, it took me quite a while to get these formulas correct. Especially the transformations of member name to text to number, took a lot of debugging. By writing down my notes I ensure you do not have to follow the same painful exercise as I did. Hopefully, you will find this post when you are in need for this info.

-----
about the author
mm

Arthur van den Berg

LinkedIn

I love the calculation power of Essbase and the financial problems you can solve with it. I have developed, implemented and improved dozens of EPM solutions with Essbase at its core and I love sharing what I learned with professionals that need to build and/or support these solutions.

share this post