Ignore Value Parameter for MIN Function

ablack

When using a minimum function, it would be ideal if we could exclude specific values, or a list of values, to avoid returning zeros or another value.

 

For the aggregation function x[MIN:y] you may want to exclude zeros from the formula. Currently the workaround is to use a large number in place of zeros, eg: "IF x = 0 then 9999999999999999 else x" and then use the summary method Min. Added an additional exlude parameter would eliminate the need for this workaround.

 

Another alternative I've seen is using a subset on the list, but this isn't ideal when you think about values intersecting another dimension, eg: time. If an item had a value one month and not the next, you would have to rapidly rebuild the subset, or you would still return zeros.

 

For the formula, it is simply MIN(item 1, item 2, item n), but adding an exclude could look something like: MIN( (item 1, item 2, item n), (exclude value 1, exclude value n) ) or something similar.

 

I've seen a number of people ask about this, but I haven't seen anyone post this as a recommendation.

 

tbeck_bx

I agree, The RANK Function, as shown below, has multiple syntax parameters to "y: include" or "z: group" which makes ranking items more effective. 

RANK(v [,w] [,x] [,y] [,z]) 

where:

• v: Source
• w: Direction: Ascending or descending order (optional)
• x: Ties: Ranking assignment (optional)
• y: Include: Whether to include the corresponding value in the ranking (optional)
• z: Group: rank the source values independently within each group (optional)

If the MIN function is structured the same way, we can eliminate the alternative solutions stated by ablack  

Also, if there was a separate MIN function specific to line items it would be beneficial as well, because if we want the minimum number in a line item, with the "include" Boolean logic, and "group" we can get the min or max value specific to different levels of a hierarchy and exclude zero values. 

 

Miran

Status changed to: In Review

usman.zia

Great Idea @ablack I've come across this issue before but have always used the workaround! Would make sense to build the functionality to support this use case. 

Gwen.pryor

Status changed to: In Review

manchiravi

I have also faced the same issue and implemented workarounds to archive the results. Better to have the solution from Anaplan instead of implementing workaround.