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 best practice (https://community.anaplan.com/t5/Best-Practices/Data-Hubs-Purpose-and-Peak-Performance/ta-p/48866), 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