Forum Discussion

jzachar's avatar
jzachar
New Contributor III
3 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 D...
Finance
  • TheJonG's avatar
    TheJonG
    3 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 III

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???

 

 

 

 

TheJonG's avatar
TheJonG
Contributor III
3 months ago

I think you changed your old code incorrectly. In the second argument of the multiply unbalanced, you need to reference the unbalanced dims. Below is the correct way:

 

  • jzachar's avatar
    jzachar
    New Contributor III
    3 months ago

    I'm sorry, I don't understand what you were referring to.  When you say "In the second argument of the multiply unbalanced" you are referring to B or C? (i took it as B) 

    I though A defined the first buffer (Driver acct), B defined the 2nd Buffer (Pct) and C defined the unbalanced Dims, dim member in Pct buffer 'B' that don't exist in the driver buffer 'A': i.e. U1=None, U2=None, U3=None, U4=None, U6=PlanLoad & U7=None.  Maybe a better way for me to say it is the members that are different than the driver members needed to lookup the PCT value. 

    In the old code that variable tbFactorPctDims contained the full POV of the PCT value...

    And now the new variable tbFactorUnbalancedDims contains only the Dimensions and value that the PCT buffer does not have, or rather, the dim and value needed to find the PCT that are different than the Driver buffer values

    To look back at the data buffers, it is the circled (red) PCT Buffer dim/members that don't exist in the Driver buffer)

    (Pct Buffer)

    (Driver Buffer)

    Am I incorrect in my thoughts on the syntax of the MultipiyUnbalanced parameters and what they represent?

     

     

    • jzachar's avatar
      jzachar
      New Contributor III
      3 months ago

      I did change the MultiplyUnbalanced statement to include may new tbFactorUnbalanced variable which only includes the dimensions/members that are in the PCT buffer, but not the Driver Buffer:

       

      (Newest statement)

      But I still do not calculate anything for my factor account.  I think I'm back to "going in circles".