Median Calculation/Exclude NAN from MAX function

Highlighted
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?

 

clipboard_image_0.png

clipboard_image_1.png

 

Thanks

 

Mark

2 REPLIES 2
Highlighted
Master Anaplanner/Community Boss

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

001.png

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

Highlighted
Community Boss

Re: Median Calculation/Exclude NAN from MAX function

Of course Vignesh...too simple!

 

Thanks