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

Accepted answers

All comments

JaredDolich

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

davargas

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

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

@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

@davargas

Go to the Settings for Time and enable All Periods.

MarkWarren

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-calendar

davargas

@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

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:

Quick Links

Can you help?

Unanswered questions

Add list members to a selective access list
Hi, I am working on a solution where the User should only have access to write/edit the list members that they add from an Action on the frontend. This action adds another list member to a selective access list. The problem is, in order to run the action, you need access to the entire list, so you would see all list…
Dimensionality Issue
Hi ALL, In my model, I have Need stored at a 'D3' RS Group level. I need to use this Need in another module at the G5 Location level. I currently have a mapping module where each G5 Location maps to a D3 RS Group. This Module uses D2 Role as dimension. but the line item I've to find the RS Group (Find D3 RS Group = 'FIND…
Formula to return Connection details...
I couldn't find any formula to do this, but I want a way to put on the page/grid the connection details. This is so I know what connection I'm in rather than going into Connections details to see it. Is there an XL3 formula to return this value?