Dynamically calculate YTD average

[AndreaF]

Hi,

I've build an dynamically calculated account that should show the FTE YTD average, so that if the month is February it returns the average FTE of the Jan and Feb, if the month is March it returns the average FTE of the Jan, Feb and March, etc.

It makes things a bit more complicated the fact that the FTE account is a balance account, so I cannot simply use V#YTD, because that would return the same value as the Period view.

My draft version of the formula looks like this:

(This is only intended to show you what i am trying to achieve. It only list the first 3 months and if only works for 2023).

'Return the YTD average of the FTE account
Dim iCurrPeriod As Integer = api.Time.GetPeriodNumFromId

If iCurrPeriod=1 Then
Return api.Data.GetDataCell("A#PP_Acc_FTE:T#2023M1")
Else If iCurrPeriod=2 Then
Return api.Data.GetDataCell("(A#PP_Acc_FTE:T#2023M1 + A#PP_Acc_FTE:T#2023M2) / 2)")
Else If iCurrPeriod=3 Then
Return api.Data.GetDataCell("(A#PP_Acc_FTE:T#2023M1 + A#PP_Acc_FTE:T#2023M2 + A#PP_Acc_FTE:T#2023M3) / 3)")
End If

How can I improve the formula? In particular, how do I sum the value from January up to the current period?

Any suggestion is appreciated. Thank you

[AndreaF · Accepted solution]

I have just noticed I can use the YearAvgToDate member from the View dimension to return the average. The members seems to work fine with balance accounts too!

Now my formula is much much simpler:

'Return the YTD average of the FTE account
Return api.Data.GetDataCell("A#PP_Acc_FTE:V#YearAvgToDate")

[ChrisLoran]

These view members are fine , but be careful because they will treat blank cells as zero amount, and give an erroneously low average, as opposed to discarding blanks in the calculation. Sometimes you want to do an average of stored data cells only and ignore blanks. In which case you have to iterate over the periods and test the CellStatus.