03-08-2024 03:59 AM
Hello,
The client has a calculation for the planning process where I used the prior period values in the calculation for current month values. Here is the data buffer I created:
Dim OpCostsDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(RemoveZeros(F#Top:O#Top:T#POVPrior1:I#Top:U5#None:U6#None:U7#Top:U8#Top),A#Op_Costs.Base,U1#Top.Base, U2#Top.Base)
Then, the client had another idea to accommodate where if the calculation is for first month (M1) take a 3-month average and then for the calculation of the subsequent months (M2 – M12) revert to the original logic of trending on prior month – POVPrior1.
I added this logic, where I included an IF statement:
Dim OpCostsDataBuffer As DataBuffer = New DataBuffer ()
If POVPERIODNUM = 1 Then
OpCostsDataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(RemoveZeros(F#Top:O#Top:T#" & Year1 & "Q4" &":I#Top:U5#None:U6#None:U7#Top:U8#Top),A#Op_Costs.Base,U1#Top.Base, U2#Top.Base)")/3
Else
OpCostsDataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(RemoveZeros(F#Top:O#Top:T#POVPrior1:I#Top:U5#None:U6#None:U7#Top:U8#Top),A#Op_Costs.Base,U1#Top.Base, U2#Top.Base)")
End If
The formula works, but the consolidation time increased significantly. Is there another workaround for this?
Thanks!
Solved! Go to Solution.
03-08-2024 05:19 AM - edited 03-08-2024 05:27 AM
Some of the slowdown is likely inevitable, for the simple reason that data is stored as YTD; so when you look at quarters, you're doing more work just to sum things up. Following the advice above should help you minimize the extra time, but it will likely never be as fast as it was before. You'll have to find gains elsewhere (e.g. executing only on specific Consolidation members, etc).
03-08-2024 05:19 AM - edited 03-08-2024 05:27 AM
Some of the slowdown is likely inevitable, for the simple reason that data is stored as YTD; so when you look at quarters, you're doing more work just to sum things up. Following the advice above should help you minimize the extra time, but it will likely never be as fast as it was before. You'll have to find gains elsewhere (e.g. executing only on specific Consolidation members, etc).
03-08-2024 06:03 AM
Hello,
I tried to replace the /3 and put it inside the GetDataBufferUsingFormula, but got an error. This is how I placed it:
OpCostsDataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(RemoveZeros(F#Top:O#Top:T#" & Year1 & "Q4" &":I#Top:U5#None:U6#None:U7#Top:U8#Top),A#Op_Costs.Base,U1#Top.Base, U2#Top.Base)"/3)
This is the error: Conversion from string ":I#Top:U5#None:U6#None:U7#Top:U8" to type 'Double' is not valid. Input string was not in a correct format.
I am having 'restrictions' placed for entity, consolidation members, but having this additional logic created, it impacted the overall times.
Thank you!
After I put it
03-08-2024 07:15 AM - edited 03-08-2024 07:19 AM
you have to put the " / 3" outside the parentheses, i.e. ....U8#Top),A#Op_Costs.Base,U1#Top.Base, U2#Top.Base) / 3"
I'd still use V#MonthAvg instead, though.
03-08-2024 08:02 AM
I tried to test the V#QuarterAvg, but it does not get the Average when picking T#2024Q4 for example, it puts the average on Q4 periods (M10, M11 and M12), but T#2024Q4:V#QuarterAvg retrieved the total amount:
I needed to see the 208K put on T#2024Q4:V#QuarterAvg to replace the "/3" in the BR.
03-08-2024 08:08 AM - edited 03-08-2024 08:11 AM
What's T#2024Q4:V#MonthAvg ...?
Also remember to specify the View in your destination, to determine where numbers will go.
03-08-2024 08:12 AM
It seems like T#2024Q4 type is not valid for V#MonthAvg:
03-08-2024 10:36 AM
Yeah sorry, had a brain fart. But, since you're hardcoding the period anyway, you could use T#2024M12:V#QuarterAvg, which will give you the right number.
03-08-2024 06:11 AM
Also, the POVPERIODNUM is defined like this:
Dim POVPERIOID As Integer = api.Pov.Time.MemberId
Dim POVPERIODNUM As Integer = api.Time.GetPeriodNumFromId(POVPERIOID)
03-08-2024 07:17 AM
That's not bad but, if you don't need that number later, you can try this instead:
if api.time.IsFirstPeriodInYear() then
' ... etc etc
End if
That avoids allocating variables. Don't expect massive speedups, but any little helps...