Creating a dynamic current month flag (or previous nth month)

Previous Member

Hi all,

 

I was hoping we could get some advice on the following:

 

We have a sales cube that contains sales by month and product. We have created a dataview that shows the yearly cumulative value, which works great (block a). Now, we would like to show the previous month's yearly cumulative value. For now we created another hidden sales block with the 'last value' function in order to bring back the current month's data (block b). We can use this block in an algorithm (block c) with the formula a-b in order to get the previous month's yearly cumulative value. However, as you might expect if a product has 0 (or null) sales in the current month the last value function with take the next available one, which breaks our PYCV value.

 

What are some good ways to return the current months sales value only (dynamically - i.e. the 'current month' would have shift forward by one month every new month)? Similarly, how would this concept work to bring back the previous nth month based on today's date?

We are still quite new to board so please bear with us!

Any help/advice would be greatly appreciated.

Tim

Adam Elvin

Hi Tim

I recently worked on a solution for this, like you we wanted to select a day and then show WTD, MTD, YTD for that day and also Prior year WTD, MTD, YTD, and calculate a variance.  Of course the problem is that when you use offset for a period to date entity like week or month you get the value for the whole prior period so it is not a like for like comparison.  The solution was to create period to date reporting cubes at the day level using Nexcel,  i.e.

 

and 

Notice by changing the row grouping you reset the running total at that level.  You can save the nexel formula and call it in a porocedure to write the running total into a MTD cube and a YTD cube.

 

Then you can add the daily, weekly MTD, YTD cubes as blocks to your data view and then using the selector for any given day get the required aggregations.

 

You can also use the offset in the function tab to get prior period aggregations, i.e. MTD to the same day prior year.

 

 

Off course you may have to consider leap years, this method was applied to a working protoype so it was lacking in full production logic.

 

I hope this helps

Previous Member

Thanks Adam! Much appreciated, we'll give this a go and see where we can take it