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

Best Answer

  • ryan_kohn
    Answer ✓

    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