How do I exclude zeros from a MIN aggregation?

ryan_kohn

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:

 

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.

 

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.