Dynamically calculate YTD average

AndreaF
Contributor III

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

1 ACCEPTED SOLUTION

AndreaF
Contributor III

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!

Afone_0-1667931992938.png

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")

View solution in original post

3 REPLIES 3

AndreaF
Contributor III

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!

Afone_0-1667931992938.png

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
Valued Contributor

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.

Hi Chris - I'm trying to avoid the above in my average FTE calc, could you give me an example of how I could write out the formula to only pick up cells with stored data and ignore the blanks? Thanks, Lauren