 # how to dynamically show dates on a day dimension

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

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.

----- 