Forum Discussion

CA5's avatar
CA5
New Contributor
6 months ago

Data Buffer Calculation

Hi everyone, 

I'm trying to create a formula for an account member that includes different accounts and UDs. I'm trying to do something like this: (A#COMMODITY_GM = A#GROSS_MARGIN - A#NET_INCOME:U2#WC - A#NET_INCOME:U2#US + A#503099:U2#EC - A#FINANCE_EXPENSE:U3#OTHERGRN)

I found in the book Finance Rules and Calculations a possible solution using a Data Buffer, so I came up with the following formula:

Dim grossmarginDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("A#GROSS_MARGIN:U8#None")
api.Data.FormulaVariables.SetDataBufferVariable("GROSS_MARGIN",grossmarginDataBuffer, False)

Dim netincomeWCDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("A#NET_INCOME:U2#WC:U8#None")
api.Data.FormulaVariables.SetDataBufferVariable("NET_INCOME_WC" , netincomeWCDataBuffer , False)

Dim netincomeUSDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("A#NET_INCOME:U2#US:U8#NONE")
api.Data.FormulaVariables.SetDataBufferVariable("NET_INCOME_US" , netincomeUSDataBuffer , False)

Dim grainposDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("A#503099:U2#EC:U8#NONE")
api.Data.FormulaVariables.SetDataBufferVariable("GRAIN_POSITIONING" , grainposDataBuffer , False)

Dim finexpDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("A#FINANCE_EXPENSE:U3#OTHERGRN:U8#NONE")
api.Data.FormulaVariables.SetDataBufferVariable("FINANCE_EXPENSE" , finexpDataBuffer , False)

Return api.Data.Calculate("A#TEST_GM_GRAIN = $GROSS_MARGIN*1 ,  $NET_INCOME_WC*-1 , $NET_INCOME_US*1, $GRAIN_POSITIONING*1 , $FINANCE_EXPENSE*-1")

 

It calculates but only retrieves the result of the first argument $GROSS_MARGIN*1. Am I missing any step in the formula?

Thanks in advance, 

Camila

 

I

Finance
  • Henning's avatar
    Henning
    Valued Contributor II
    6 months ago

    Hi Camila,

    It only returns the first data buffer variable as this is the only one that has a dimensionality equal to the target on the left side of the equal sign. All others have a specified UD2 or UD3 member, which creates an imbalance.

    Asymmetric data buffers cause data explosion, which is why they have to have the same common members. 

    However, on top of that, you are using commas instead of the plus sign (+) in your Api.Data.Calculate function. The system does not know which operand to use (+, -, *, /) by itself. Nevertheless, even with "+", I would assume you still have issues.

    What might help you is ConverUnbalanced:

    Examples of Key Functions in Use (onestream.com)

    api.Data.Calculate("A#TEST_GM_GRAIN:U2#MyU2TargetMember:U3#MyU3TargetMember = ConvertUnbalanced($GROSS_MARGIN, UD2#MyU2SourceMember)*1 + ConvertUnbalanced($NET_INCOME_WC, UD2#WC)*-1 + etc...

    What I have never tried is an unbalanced operation with a second unbalanced dimension, in your case UD3. You will have to test this to see how it goes.

     

    Overall, it may be worth exploring the original requirement for setting up this calculation as well. What is it your are trying to achieve here? Is this just to have a sum of gross margin by net income in different categories? This might just as well be done in a cube view with the different items in rows and columns added together using cube view math:

    Create Cube View Column and Row Calculations (onestream.com)

     

    A side note, why do you use "Return" before the api.data.calculate? I assume this is just a normal calculation so your return should be - if necessary in the first place - follow in a later row as "Return nothing".