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

Accepted answers

VIGNESH.M

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

All comments

VIGNESH.M

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

MarkTurkenburg_*8288

Of course Vignesh...too simple!

Thanks

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…