Skip to main content
J
jzachar
Newcomer
October 11, 2024
Solved

Finance Business Rule Data.Calculate format

  • October 11, 2024
  • 3 replies
  • 0 views

I'm struggling to understand how exactly to calculate what I need, a confirmation of direction and a little guidance would be great!  I need to calculate an account value (Factored account)  from a Driver account * a Percent account.  The Driver account data contains many different dimension attributes (product, region, customer, etc).  The percent account is at the 'None' member of those different dimensions.  The calculated Factor accounts need to be at the same level (members) as the Driver account.  I'm sure (i think, lol) I need to use the data.calculate, but I'm struggling on the syntax of the function to calculate using data at 2 different account POVs, and the results residing at the same level as the Driver account.  Whether I'll need to create data buffers and manipulate / save or simple filters within the data.calculate.  I do have multiple Drivers for some Factored account (i.e F1 = (D1*Pct) + (D2*Pct) + (D3*Pct)), and multiple Factor accounts using the same Driver (i.e F1 = (D1*Pct) , F2= (D1*Pct)). A simple road map would be great, anything more would be even better.  I'd like to first get a simple example working (i.e F1 = (D1*Pct) and expand it once it works and I understand the syntax needed. Thanks in advance

Best answer by TheJonG

Both suggestions by rhankey and akatsman are good - either an Eval or a Data Buffer cell loop give you ultimate flexibility, however, they are fairly advanced techniques that can be hard to get your head around at first. My suggestion is to use the unbalanced function within an api.Data.Calculate as already mentioned. Here is how you would implement it given your example, assuming the Driver detail has detail for 3 UDs (product, region, customer).

api.Data.Calculate("A#Factor = MultiplyUnbalanced(A#Driver, A#Percent:U1#None:U2#None:U3#None, U1#None:U2#None:U3#None")

 

The result of this will produce the Factor account at the same level of detail as the driver (ud1, ud2, ud3). The Unbalanced function essentially 'fixes' the dimensions to None for the Percent buffer and multiplies the same percent against all cells of the Driver buffer. You can add multiple unbalanced functions together once you get the simple example working. Hope this helps.

 

3 replies

A
akatsman
Newcomer
October 11, 2024

Hi Jzachar,

This sounds like an ideal situation to use a data buffer. It will allow you to run through all the different dimensions of your driver account while using a get data cell for the percent account. This will allow you to do the math on each source cell of the data buffer while using the specific percent. Since you want to use the same dimensionality as the driver, utilizing the source data buffer information will be useful. You also have the ability to set output account to the factor account it should go to.

For the factor accounts that use multiple percentages, you can use if statements while looping through the data buffer to calculate that information as needed.

Thanks,
AJ

R
rhankey
Newcomer
October 11, 2024

I can think of at least two options to accomplish your need using the api.Data.Calculate() function:

api.Data.Calculate("DestinationPath = UnbalancedMultiply(FactorPath,DriverPath,UnbalancedPath)")

api.Data.Calculate("DestinationPath = Eval2(DriverPath,FactorPath)",AddessOf OnEvalBufferHandler)

Private Sub OnEvalBufferHandler(ByVal api As FinanceRulesApi, ByVal evalName As String, ByVal eventArgs As EvalDataBufferEventArgs)

'Cycle through DataBuffer1, and lookup rate from DataBuffer1 using a key that sets dims to DimConstants.None as applicable, perform multiply and write out results.

End Sub

 

UnbalancedPath in the first example provides the dimensionality to use with FactorPath for the dims that are missing from the DriverPath.

I use the Unbalanced() functions occasionally, but I normally use an OnEval hander, as it is far more flexible when the requirements take a left turn after the fact.  And I find it a whole lot easier to debug the OnEval method, whereas the Unbalanced() option saves a few lines of code but is a bit of a black-box when anything goes wrong.

 

T
OneStream Employee
TheJonGOneStream EmployeeAnswer
OneStream Employee
October 12, 2024

Both suggestions by rhankey and akatsman are good - either an Eval or a Data Buffer cell loop give you ultimate flexibility, however, they are fairly advanced techniques that can be hard to get your head around at first. My suggestion is to use the unbalanced function within an api.Data.Calculate as already mentioned. Here is how you would implement it given your example, assuming the Driver detail has detail for 3 UDs (product, region, customer).

api.Data.Calculate("A#Factor = MultiplyUnbalanced(A#Driver, A#Percent:U1#None:U2#None:U3#None, U1#None:U2#None:U3#None")

 

The result of this will produce the Factor account at the same level of detail as the driver (ud1, ud2, ud3). The Unbalanced function essentially 'fixes' the dimensions to None for the Percent buffer and multiplies the same percent against all cells of the Driver buffer. You can add multiple unbalanced functions together once you get the simple example working. Hope this helps.

 

    J
    jzacharAuthor
    Newcomer
    October 14, 2024

    Thank you for the guidance, but something isn't correct (working) on my end.  Maybe you can see it, I Don't. 

    First, to assist in analysis, I've created and dumped the buffers for the Factor PCT, Driver Acct & Factored Acct B4 calculating it:

    Factor PCT Buffer

    jzachar_0-1728910642405.png

    Driver Acct Buffer

    jzachar_1-1728910642416.png

    Factored Account Buffer (B4 calc)

    jzachar_3-1728910790473.png

    here is the code dumping 3 buffers: Factor Pct, Driver Acct & Factored Acct(B4).  Followed by the  MultiplyUnbalanced Calculate using the same filters as  the Buffer dumps.  Followed by the Factored Acct after the calc.  There seems to be no difference in Factored accts B4 & After

    jzachar_4-1728910918761.png

    Factored Account Buffer (After calc)

    jzachar_5-1728911011372.png

    I fell like my buffer dumps show me that the component buffers for the MultiplyUnbalanced exist, but that the calculation isn't working as expected.  Do you have any idea where I've gone wrong or why I'm not seeing the results I expected?  Thank you 

     

     

     

     

     

     

    J
    jzacharAuthor
    Newcomer
    October 14, 2024

    TheJonG, I did recognize a mistake I made in my above testing, I used a filter to pull the Factor PCT value and the same filter in the MultiplyUnbalanced Calculate statement.  And an issue with the Unbalanced (3rd) Parameter , I used the same filter as I used in my 2nd parameter.  I have corrected both.  Now my Factor PCT dump looks like this (only the 1 PCT value record)...

    (Factor PCT: red dimensions are unbalanced members to Driver Buffer)

    jzachar_1-1728924484767.png

    the Driver buffer is still the same...

    (Driver: Red dimensions unbalanced members to PCT buffer)

    jzachar_2-1728924747003.png

    My code has change, I've removed the parm 2 filters (tbFactorPctDims) and only include U1, U2, U3, U4, U6, U7 in the parm 3 Unbalanced members (tbFactorUnbalancedDims).  I did make 2 othe changes, in the first parm I added ':U6#Plan_Adj_Factor' to write to a new UD6 memberand the filters at the end where I set 'Is Durable' calc to true.  Unfortunately, I'm still not calculating my Factor accounts and I'm really not sure why???

    jzachar_3-1728925181829.png

     

     

     

     

    Terms & ConditionsAccessibility statement