Skip to main content
B
BenEppel
April 26, 2023

Using the Trailing3MonthAvg View Member in a Data Buffer Across Years

  • April 26, 2023
  • 3 replies
  • 0 views

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)

 

    3 replies

    J
    JackLacava
    April 26, 2023

    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.

    B
    BenEppelAuthor
    April 27, 2023

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

     

    Thank you for the response!!

    J
    JackLacava
    April 27, 2023

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

    M
    Mike_Sabourin
    May 16, 2023

    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

    B
    BenEppelAuthor
    May 16, 2023

    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
    S
    sabrinacook
    August 11, 2025

    Has anyone seen a solution posted for this yet?

    Terms & ConditionsAccessibility statement