How to calculate Average on the Non-zero and Non-null values using Advanced Formulas

[kvsreekar]

Dear SAC Experts,

Using Advanced Formulas, I would like to calculate the Average FTE value of the 'Actual' months and store the result in the the remaining 'Forecast' months.

The Actual & Forecast months are flagged by a property (FORECAST_FLAG) on the MONTH dimension.

Using the below code I was able to calculate it but I have hardcoded the number of Actual Months (to 10) and I would like to make it dynamic based on the number of Actual Months.

Result look like this:

Also, I would like to ignore the Zero & Null values and calculate average only based on the populated values, i.e. not always divide the total by the same number of months.

Could anyone of you please help with the code.

Thanks,

Sreekar

[kvsreekar]

I think I got it working. But if someone has other ideas I welcome it.