04-26-2023 05:40 PM - last edited on 05-02-2023 10:39 AM by JackLacava
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)
04-26-2023 06:57 PM - edited 04-26-2023 06:58 PM
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.
04-26-2023 08:10 PM
The version is 7.2.2. We already have plans to upgrade shortly so that works out.
Thank you for the response!!
04-27-2023 05:51 AM
Sadly I'm seeing the same on 7.4.1 😓 I'll open a case with Support and let you know how it goes...
05-20-2024 09:04 AM
Hi Jack,
Glad I found this post as I thought it was my syntax that was giving my wrong numbers. Do you know if this has been corrected in version 8?
Thanks
07-11-2024 12:35 PM
I would also be interested to know if it has been solved in versions 8.1 & 8.2
05-16-2023 05:25 PM
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
05-16-2023 05:46 PM - last edited on 05-17-2023 05:11 AM by JackLacava
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