AnsweredAssumed Answered

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

Question asked by timbayer on Jun 27, 2018
Latest reply on Jul 2, 2018 by timbayer

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.