Exclude negative data in member formula — Cloud Customer Connect
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

Exclude negative data in member formula

Summary:

hello,

Need some advise on how to exclude months with negative values in the data set when taking an average. We are currently doing this within Excel's functions.

In the Period dimension, I have a member formula. This works perfectly when all 12 months data are positive:

"
@avgrange( skipnone, @currmbr("Account")->@currmbr("CostCenter"), "Jan":"Dec" );"

However, due to seasonality, at times we get a negative value in some accounts on some months, which I need to exclude from the average calculation, so if there is one negative data in one month, we need to exclude that month from the average calculation, and sum up the total divided by 11 months.

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!