How to Find Max for Specific line item
I am trying to create a module with the following line items (dimensioned by products in pages and line items in rows, time does not apply). when a product is select from pages:
Max slots - display max value across any month
When - display the month that the 'Max' appears
Width Factor% - user input
Width Interval - display months where the values for selected product are > 'Width Factor%' of Max
This module is sort of a reporting module for my main module that shows the number of slots each product uses each month. Please help with the formulas for this reporting module(except for 'width factor%). thank you
*note that 'when' and 'width interval' should both be able to display multiple months if necessary
Answers
-
If you're using a system module (the "S" in DISCO) then you can use the aggregation function MAX. Works exactly like the SUM function. If you need to find the max within a single line item it would look something like this:
LINE ITEM[MAX.LIST ITEM]. Ideally this value would be returned in the system module or in a calc module, not the planning module as you don't want to repeat the calculation over and over.
The list item should be a reference to your system module. The list should be the same list in your planning module as well so Anaplan can return the value to you.
0 -
Hi @JaredDolich I dont want to hard code the formula because the max needs to change upon the product being selected in pages. In my source module, I have product list and line item 'slots' on rows and time in months in columns. In my target module, I have product list in pages and line items in rows (time does not apply). The line items are:
Max Slots
When
%Width Factor
Width Interval
I want Max slots to show the max value for the selected product across any month. And I want when to show which month the max value occurs
0 -
Set the summary option on your source line item "Slots" to MAX, then in "Max Slots" you can do Source.Slots[SELECT: TIME.All Periods]
0 -
@MarkWarren Thanks for the response. When I use TIME.All Periods in a formula, it reads an error that says it is not present in the model's time scale. Do you know a work around to this? Also, I am looking into using TIMESUM as a solution.
0 -
Go to the Settings for Time and enable All Periods.
0 -
You have to set Total Of All Periods in the calendar/time settings
This is the Anapedia page for this:
https://help.anaplan.com/c9e69e39-0220-46d6-b756-e68e9158bb8c-Set-the-Calendar-Months-Quarters-Years-calendar0 -
@MarkWarren @JaredDolich @rob_marshall Thank you all for your suggestions. For me, the TIMESUM function worked perfectly. I just have one more question in relation to this. Alongside pulling the max value for each product, I want to display the month that that value occurs. My model's time is setup as Feb 21-Jan24. Any tips?
0 -
We can do that in a similar way using our newly created max value.
I put this together to demo it better:
In the source we find the data value that matches the max (from target module) and for that value show the month period; this line item needs first non-blank summary type. Note here that if second product had the same max value we'd show the month from the first product.
Here's the data:
and the result:
0
