Community Boss

## Median Calculation/Exclude NAN from MAX function

Hi team,

I'm attempting to calculate the Median revenue per customer over a period of 4 months (excluding negative revenue months due to credit notes etc) but cannot obtain the Max value per customer due to it returning the NaN value (if there is 1)

My process has been:

1) Module dimensionalised by Customer & a dummy Month list. Then 3 line items, Revenue per month, Greater than zero boolean & then RANK by Customer excluding the values less than zero.

2) Create a Module dimensionalised by Customer List & with a line item returning the MAX value from the previous module. This always returns the NaN value rather than the greatest value

Is there a way to exclude the NaN value from the Max calculation?

Thanks

Mark

1 ACCEPTED SOLUTION

Accepted Solutions
Certified Master Anaplanner

## Re: Median Calculation/Exclude NAN from MAX function

Hi Mark,

Try this,

Create another line item to exclude NAN as shown below and use Final Rank to calculate the MAX

Check this link for more ideas, it has a similar question.

https://community.anaplan.com/t5/Anaplan-Platform-Discussions/NaN-formula/m-p/42400#M4539

Regards,

Vignesh M

2 REPLIES 2
Certified Master Anaplanner

## Re: Median Calculation/Exclude NAN from MAX function

Hi Mark,

Try this,

Create another line item to exclude NAN as shown below and use Final Rank to calculate the MAX

Check this link for more ideas, it has a similar question.

https://community.anaplan.com/t5/Anaplan-Platform-Discussions/NaN-formula/m-p/42400#M4539

Regards,

Vignesh M

Community Boss

## Re: Median Calculation/Exclude NAN from MAX function

Of course Vignesh...too simple!

Thanks