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

 

Finance
  • JackLacava's avatar
    JackLacava
    Honored Contributor
    2 years ago

    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's avatar
      BenEppel
      New Contributor II
      2 years ago

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

       

      Thank you for the response!!

      • JackLacava's avatar
        JackLacava
        Honored Contributor
        2 years ago

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

  • Mike_Sabourin's avatar
    Mike_Sabourin
    Contributor II
    2 years ago

    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