Calculation of MODE in Anaplan

PaulRitner
PaulRitner
in Modeling

Does anyone know how to obtain the MODE of a set of values (in Anaplan)?

 

For example, lets say I have a list of employees and each employee has a salary.  I'd like to find the salary that occurs most often in a group (i.e. the MODE).  Working with MODE in Excel, there will be times when a MODE value doesn't exist (because all of the values are unique).  Our use-case frequently has recurring values, so we want a way to dynamically find them.  I considered using a list to set the values from (because then we could get a count of how many times each list item is used), but that probably won't scale well and seems a little silly.

 

Cheers!

Tagged:

Answers

  • SGnatovich
    SGnatovich

    Hi Paul,

    How many salaries are we talking about? A combination of NOT/ISFIRSTOCCURRENCE and RANK could work.

  • Jaakko
    Jaakko

    Hi,

     

    I have one example you can start with.

     

    Let's say we have a module called "Salaries" with a list "Employees" and line item "Salary". Time can also be included here if you want to have periodical values calculated.

     

    Add another line item "Count" and formula "IF Salary <> 0 THEN 1 ELSE 0" to it. We will cumulate the occurrences of each salary from this line item. Without the formula we would count zeros which we probably don't want to do.

     

    Add third line item "Total Count" and formula "RANKCUMULATE(Count, Count, DESCENDING, TRUE, Salary)" to it. This will cumulate the occurrences from the "count" line item. It will cumulate the flags of value "1" so that it groups cumulation by salary.

     

    Add fourth line item "Rank" and add formula "RANK(Total Count)" to it. List members with value "1" in this line item have "mode salary" in salary line item. If there are many salaries with equal number of occurrences, all of them will have value "1" here.

     

    At this point you will know which list members (employees) have the "mode salary" but you will not have the value itself in one cell. If you only want to show this in a dashboard, you could use filtering using "rank" line item. If you need the mode value in one cell, one (silly?) solution could be adding again line item "Mode" which would have formula "IF Rank = 1 THEN Salary ELSE 0" and aggregation method of MAX (of course this would not work in case of multiple mode values, it would only give the largest one). Then you could use the value in another module with formula "Salaries.Mode" or "Salaries.Mode[SELECT:Employees.Total]".

     

    This is not probably ideal solution but hopefully you'll get some ideas from it! 🙂

     

    Br,
    Jaakko

     

     

  • PaulRitner
    PaulRitner

    Hi, I think this will work & am setting up time this week to test it.  Will confirm once finished.  Thanks! Paul

  • mcconachie
    mcconachie

    Paul, 

     

    I have needed to find a MODE for a couple models now. The way I have been calculating the Mode is by turning the numbers into a text string concatenated with the dimensions that I need to find the mode across. 

     

    i.e. Find the Mode of a Price by Product, by Territory.

    Step 1: Load File with unique ID name KEY ID

    Step 2: Concatenate Product&Territory&Text(Price)

    Step 3: Firstoccurence(Product&Territory&Text(Price), KEY ID)

    Step 4: Import concatenation of Product&Territory&Text(Price) into a new list name Mode List

    Step 5 Mode List Finditem: Finditem(Product&Territory&Text(Price), Mode List)

    Step 6: Add a line item with Formula = 1 and name it Count

    Step 7: Final Mode: Count[Sum: Mode List]

    Step 8: Run a Max Formula on Final Mode by Product by Territory

    Step 9: If Max = Final Mode then Sum Price by Product by Territory

    I understand that this takes a couple steps to run the mode, let me know if you have a simple method to run a mode across multiple dimensions!

     

    Thanks! 

    Morgan

     

Categories