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.
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! 🙂
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.