Sum Transactions Based on Properties Between Two Dates (Days, Weeks, Months)
An often requested output of a trade promotions use case is to understand sales between two specific dates— be it days, weeks, or months. The solution below has one dimension in it but would work equally well with additional dimensions, such as customer or supplier.
Having loaded in the transaction detail following this best practice, a module dimensioned by time and relevant other dimensions will be the outcome.
Into this module a new line item cumulating the existing metrics is going to be added, this will enable the analysis between two dates to be achieved
For both line items, set summaries to none:
By doing this, the model size is significantly smaller and calculations aren’t being made unnecessarily. In this model, which is 5 years and 10 products, this module is 15% smaller with summaries to none (36,600 cells) versus with them on (43,300 cells).
For the cumulate formula, use the below:
For small datasets, the use of cumulate is faster, but for long time ranges, the below is recommended because of how the Hyperblock works, and if one number changes there’s less impact (see 2.02-10 in Planual):
The below is then the outcome.
Create a module dimensioned on a new list called trade promotions analysis that contains line items products, plus any other required transaction properties - start date, end date, start date -1 and quantity.
The reason for the start date -1 is that to calculate the amount between the two dates the cumulate value at the end, less the cumulate at the start, will not be enough as it will be missing the first date. So, start date -1 is needed.
This formula then looks at the time dimensioned module looking up the values inputted in the product, end date line items, and subtracts the value for the same look-ups with the calculated start date -1.
The below is then the output and can be published to boards as appropriate
The above solution works for all time formats, but if weeks/months are needed, then just turn on summary for time on the cumulate column. This is a good option if analysis is required for any time period and gives the flexibility in the future if required in line with the S of PLANS.
All that needs to be done is to format the date fields in the analysis module appropriately and the calculations will continue to work.
The above will allow you to perform that all important analysis on product performance on a more adhoc basis rather than relying on automated time sums.
Comments
-
Nicely handled. I'm certain I will be using this. I keep a list of solutions to specific use-cases in One Note, so I've promptly added this one.
And, personally, I really appreciate the detail and the extra work you did to show the statistics of using different methods, e.g., turning off summaries, when to use CUMULATE vs PREVIOUS). This stuff really matters because they're inevitable follow-up questions to these types of solutions.
The only other thing I wanted to mention is that I noticed you had a lot of images in this post. If you're like me, you know that writing these best practices takes much longer than it does to actually solve it in Anaplan. It's all those images you have to create and if anything changes, they can cascade and before you know it you're replacing half the images, keeping them in the right order, naming them so you can find them in your subdirectory, etc... I guess what I'm trying to say is thanks. I know it's a lot of work to write these but the value it brings to people like me is priceless. Thank you @andrewtye.
3
