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.