Forum Discussion

BenEppel's avatar
BenEppel
New Contributor II
2 years ago

Using the Trailing3MonthAvg View Member in a Data Buffer Across Years

Hello, I was wondering if anyone has had any success using Trailing3MonthAvg View member within a data buffer when the trailing 3 months cross over years. When I run this for a period like June eve...
Finance
Mike_Sabourin's avatar
Mike_Sabourin
Contributor II

As a workaround, you could just add the last 3 periods together in a running total and then divide by 3. 

Something like:

Dim Period1 As Decimal = api.data.GetDataCell("Cb#XXXXXX:T#Pov:A#Direct_HC:S#Actual:E#XXXXX:F#EndBal_Input:V#Periodic:O#BeforeAdj:I#None:U3#Top:U4#Top:U5#Total_Adj_Alloc:U2#" & U2 & ":U7#Top:U8#None").CellAmount
Dim Period2 As Decimal = api.data.GetDataCell("Cb#XXXXXX:T#PovPrior1:A#Direct_HC:S#Actual:E#XXXXX:F#EndBal_Input:V#Periodic:O#BeforeAdj:I#None:U3#Top:U4#Top:U5#Total_Adj_Alloc:U2#" & U2 & ":U7#Top:U8#None").CellAmount
Dim Period3 As Decimal = api.data.GetDataCell("Cb#XXXXXX:T#PovPrior2:A#Direct_HC:S#Actual:E#XXXXX:F#EndBal_Input:V#Periodic:O#BeforeAdj:I#None:U3#Top:U4#Top:U5#Total_Adj_Alloc:U2#" & U2 & ":U7#Top:U8#None").CellAmount
Dim Avg As Decimal = (Period1 + Period2 + Period3)/3

BenEppel's avatar
BenEppel
New Contributor II
2 years ago

I ended up doing something very similar! I added logic to manually calculate the average for only the time periods that cross years.

If Not monthNumber = "2" And Not monthNumber = "3" Then

    Dim startingdb As String = "Cb#XXXXXX:T#" & TimePrior & ":A#" &
        Denominator & ":S#Actual:E#XXXXXX:F#EndBal_Input:V#Trailing3MonthAvg"
    db_Denominator = api.Data.GetDataBufferUsingFormula(
        "RemoveZeros(FilterMembers(" & startingdb & ", " & filter1 & ", " & filter2 _
        & "))",, True)

Else

    Dim startingBuffer2m1 As String = "Cb#XXXXXXX:T#" & TimePrior & ":A#" _
        & Denominator & ":S#Actual:E#XXXXXXXX:F#EndBal_Input:V#Periodic"
    Dim startingBuffer2m2 As String = "Cb#XXXXXXX:T#" & TimePrior2 & ":A#" _
        & Denominator & ":S#Actual:E#XXXXXXXX:F#EndBal_Input:V#Periodic:"
    Dim startingBuffer2m3 As String = "Cb#XXXXXX:T#" & TimePrior3 & ":A#" _
        & Denominator & ":S#Actual:E#XXXXXX:F#EndBal_Input:V#Periodic:"
    Dim Denominator1 As DataBuffer = api.Data.GetDataBufferUsingFormula(
        "RemoveZeros(FilterMembers(" & startingBuffer2m1 & ", " & filter1 & ", " & filter2 _
        & "))",, True)
    Dim Denominator2 As DataBuffer = api.Data.GetDataBufferUsingFormula(
        "RemoveZeros(FilterMembers(" & startingBuffer2m2 & ", " & filter1 & ", " & filter2 _
        & "))",, True)
    Dim Denominator3 As DataBuffer = api.Data.GetDataBufferUsingFormula(
        "RemoveZeros(FilterMembers(" & startingBuffer2m3 & ", " & filter1 & ", " & filter2 _
        & "))",, True)
    api.Data.FormulaVariables.SetDataBufferVariable("Denominator1", Denominator1, False)
    api.Data.FormulaVariables.SetDataBufferVariable("Denominator2", Denominator2, False)
    api.Data.FormulaVariables.SetDataBufferVariable("Denominator3", Denominator3, False)

    Dim db_DenominatorAvg As New DataBuffer
    db_DenominatorAvg = api.Data.GetDataBufferUsingFormula(
        "($Denominator1+$Denominator2+$Denominator3)/3")
    'db_DenominatorAvg.LogDataBuffer(api, "db_DenominatorAvg" & Denominator, 1000)
    db_Denominator = db_DenominatorAvg
End If