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

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

  • 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

  • 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]

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

  • @davargas 

    Go to the Settings for Time and enable All Periods.

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

  • 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