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.