Dynamically calculate YTD average
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
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")
