Forum Discussion

jzachar's avatar
jzachar
New Contributor
2 months ago

Finance Business Rule Data.Calculate format

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

Finance
  • TheJonG's avatar
    TheJonG
    2 months ago

    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.

     

  • TheJonG's avatar
    TheJonG
    Contributor III
    2 months ago

    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.

     

    • jzachar's avatar
      jzachar
      New Contributor
      2 months ago

      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

      Driver Acct Buffer

      Factored Account Buffer (B4 calc)

      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

      Factored Account Buffer (After calc)

      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 

       

       

       

       

       

       

      • jzachar's avatar
        jzachar
        New Contributor
        2 months ago

        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)

        the Driver buffer is still the same...

        (Driver: Red dimensions unbalanced members to PCT buffer)

        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's avatar
      jzachar
      New Contributor
      2 months ago

      TheJonG, I figured out my issue, it had to do with different constraint on the UD1 for the two accounts being used.  Thank you for your time.

  • akatsman's avatar
    akatsman
    New Contributor III
    2 months ago

    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

  • rhankey's avatar
    rhankey
    Contributor
    2 months ago

    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.