Using the Trailing3MonthAvg View Member in a Data Buffer Across Years

[BenEppel]

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 everything works great, but when I run this in Jan or Feb I get the object reference not set to an instance of an object error. When switching the View member to YTD everything works fine. Below is an example of the code. Thanks!!

Dim filter1 As String = "U1#XXXXX.Base"
Dim filter2 As String = "U6#XXXXXBase"
Dim U2 As String = "XXXXX"
Dim test1 As String= "Cb#XXXXXX:T#2023M1:A#Direct_HC:S#Actual:E#XXXXX:F#EndBal_Input:V#Trailing3MonthAvg:O#BeforeAdj:I#None:U3#Top:U4#Top:U5#Total_Adj_Alloc:U2#" _
   & U2 & ":U7#Top:U8#None"

Dim test As DataBuffer = api.Data.GetDataBufferUsingFormula( _
    "RemoveZeros(FilterMembers(" & test1 & ", " & filter1 & ", " & filter2 & "))",,True)

[JackLacava]

I'm seeing something like that on 7.2.2, if you're on a later version you might want to raise it with Support. An error is generated deeper down and the buffer is never returned.

As a workaround, CubeViews don't seem to have a problem producing data for that View on any period, so in theory one could build a CubeView or CubeViewMD DataAdapter, then fetch the recordset with GetAdoDataSetForAdapter. It will be super-slow and hacky but it will give you numbers.

[BenEppel]

The version is 7.2.2. We already have plans to upgrade shortly so that works out.

Thank you for the response!!

[JackLacava]

Sadly I'm seeing the same on 7.4.1 😓 I'll open a case with Support and let you know how it goes...

[Michel_Sabourin]

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]

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