Account formula help

[NicoleBruno]

SOURCE: ONESTREAM CHAMPIONS

Hi all,

I have a very random request for anyone who may have the time to review and hopefully point me in the right direction.

The formula calc is an on account and is unbalanced - I’m specifying more dimensions on the right side of the equation than the left (T#, E# and C#) which is giving me a data explosion error. I have examples of formulas currently in use where T# is on one side but not the other and the formula works fine so I’m guessing it’s an issue with E# and C# but I can’t seem to figure out how to make it work

Here’s the formula:
If Not api.Entity.HasChildren And api.Cons.IsLocalCurrencyForEntity Then
Dim vEnt As String = api.Pov.Entity.Name
Select Case vEnt
Case “ENT_1095”
api.Data.Calculate(“A#520_211010:V#Periodic:O#Import:F#EndBal:U1#NABalanceSheet:U3#Source:U4#None:U5#None:U6#None:U7#None:U8#None =
A#ACC_SAP_134450:T#POVPriorYearM12:E#ENT_2000:C#CAD:V#Periodic:O#Import:F#EndBal:U1#NABalanceSheet:U3#Source:U4#None:U5#None:U6#None:U7#None:U8#None
* A#ClosingRate:E#ENT_2000:C#CAD:V#Periodic:O#Top:F#EndBal:U1#NABalanceSheet:U3#Source:U4#None:U5#None:U6#None:U7#None:U8#None”,“I#Top.base”,“U2#Paladin.base”,True)
End Select
End If

Any thoughts on how to resolve? Any info or feedback would be much appreciated!!

Some additional background:
-There are members in the values above (A#ClosingRate and A#ACC_SAP_134450 with the respective dimensions specified)
-The calc works fine if specify one I# and one U2# member though I must update the formula as follows:
Dim v134450InFormula As Decimal = api.Data.GetDataCell(“A#ACC_SAP_134450:T#POVPriorYearM12:E#ENT_2000:C#CAD:V#Periodic:O#Import:F#EndBal:U1#NABalanceSheet:U3#Source:U4#None:U5#None:U6#None:U7#None:U8#None”).CellAmount
Dim vClosingInFormula As Decimal = api.Data.GetDataCell(“A#ClosingRate:E#ENT_2000:C#CAD:V#Periodic:O#Top:F#EndBal:U1#NABalanceSheet:U3#Source:U4#None:U5#None:U6#None:U7#None:U8#None”).CellAmount

api.Data.Calculate(“A#520_211010:V#Periodic:O#Import:F#EndBal:U1#NABalanceSheet:U3#Source:U4#None:U5#None:U6#None:U7#None:U8#None = “& v134450InFormula &”*”&vClosingInFormula)

[ckattookaran]

You could try using Unbalanced Math and see if that helps.
Api.Data.Calculate(“A#TargetAccount =
AddUnbalanced
(A#60000,A#41000:O#Top,O#Top)”)

Page 97 in 6.3 Design Guide

[BrassRoch]

So you’re trying to populate all time periods across all years, or base periods in POVYear? Try an IF for base periods in the POVYear.

[NicoleBruno]

Thanks, Celvin - can I ask where the screenshot is from? I tried googling variations of unbalanced math and vb.net and didn’t find anything remotely as useful as that.

Rochelle, the formula is on an account so it’ll run on a consolidation for a specific scenario and time period.

Also, ClosingRate is a dynamic calc so I’ve updated the formula to specify the I# and U2# for that member and now I’m no longer getting a data explosion error but the calculation isn’t actually running. Feel free to throw any suggestions my way and I’ll do some testing! In the process of testing now so I appreciate all the feedback I can get.

Thanks!

[NicoleBruno]

Thanks for the suggestions, guys - I got it working fine so please feel free to ignore this thread! 

[ckattookaran]

Sorry, that is from the OneStream design guide, for future reference.