How do I exclude zeros from a MIN aggregation?

I am building an output module to show some statistics around account pipeline by industry. I want to be able to show the smallest pipeline. Since some accounts have zero pipeline, the standard MIN function returns zero, but I want it to return the MIN excluding zeros. How can I do this?

Here's an example of what I'm looking at:

Anaplan - MIN with Zero.PNG

Accepted answers

ryan_kohn

One way to solve this is to use a staging line item to replace the zeros with an abitrarily large number. Then you can point your MIN formula to the staging line item. Note that if *all* the values are zero for a dimension, the formula will return the arbitrarily large number, so you will likely want to take an extra step to replace the result with zero in those cases.

See screenshot below for an example.

Anaplan - MIN without Zero.PNG

All comments

ryan_kohn

See screenshot below for an example.

Anaplan - MIN without Zero.PNG

TristanS

@ryan_kohn I know this is such an old post but alternatively you can introduce a dummy list and do a minimum against that dummy list.

Step 1: create dummy list with one value in it … e.g. Temp

Step 2: Add line item "Dummy Aggregator" with format "dummy" list in "Account Properties for Aggregation"

Step 3: set formula to line item "Dummy Aggregator" to

IF 'XYZ Inc. Pipeline' = 0 then blank else Dummy.Temp

Step 4: Add "dummy" list in Applies to of module "Pipeline Statistics - Industry"

Step 5: Update formula for "Min w/o Zeros" line item to

Account Properties for Aggregation.'XYZ Inc. Pipeline'[MIN: Account Properties for Aggregation.Dummy Aggregator]

Was about to use your suggestion but I am building on Polaris and that solution would have taken up unnecessary space so ended up with this one instead.

ryan_kohn

@TristanS Nice alternate solution! Note that there some additional steps in order to get this to work per the initial requirements.

In Step 5, the formula still need to include the Industry mapping in order to generate the MIN by industry:

Account Properties for Aggregation.'XYZ Inc. Pipeline'[MIN: Account Properties for Aggregation.Industry, MIN: Account Properties for Aggregation.'Aggregation Item for Pipeline w/o Zeros']

Additionally, note that for any Industry that is entirely zero values, the result returned will be "Infinity", which would need to be handled in the final result. You can calculate and store the Infinity value in a system module.

Here's a screenshot comparing the raw result with converting the Infinity values to zeros:

Whether or not this approach is better in Polaris would depend on the shape of the source data as well as the dimensionality of the target line item.

Quick Links

Can you help?

Unanswered questions

Sorting General List
Hi, I wonder if there's a way I can sort my general list alphabetically. The list is used as a drop down menu in a line item module and I want the drop down menu to be easy to navigate. I've already tried the Order List action. I already have a module with a text-formatted line item based on that list. But when I try to…
Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…