How to Find Max for Specific line item

davargas
New Contributor

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

 

8 REPLIES 8
JaredDolich
Moderator

@davargas 

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.


Jared Dolich
davargas
New Contributor

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

MarkWarren
Expert

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]

davargas
New Contributor

@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.

rob_marshall
Moderator

@davargas 

Go to the Settings for Time and enable All Periods.

MarkWarren
Expert

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...
davargas
New Contributor

@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?

MarkWarren
Expert

We can do that in a similar way using our newly created max value.
I put this together to demo it better:

MarkWarren_0-1659520235139.png

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:

MarkWarren_2-1659520570821.png

and the result:

MarkWarren_1-1659520373565.png