Forum Discussion

Marco's avatar
Marco
Contributor II
24 days ago

How can I subtract the result of two DIVIDE operations within a CALCULATE function?

Hi Everyone.

I have the following CALCULATE expression, but it’s returning no results. The source values are not zero, so I’m not sure if the way I’m using the DIVIDE function is causing the issue, or if there is another problem

Dim DBCInner As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#RETBUYBACK:E#" & strEntTx3 & ":C#Local:O#Top:U2#INNER:UD3#COUNTRY_RPT + A#423100:E#" & strEntTx3 & ":C#Local:O#Top:U2#INNER:UD3#COUNTRY_RPT + A#411000:E#" & strEntTx3 & ":C#Local:O#Top:U2#INNER:UD3#COUNTRY_RPT)")
api.Data.FormulaVariables.SetDataBufferVariable("DBCInner",DBCInner,False)
												

Dim regionalRpt As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#RETBUYBACK:E#" & strEntTx3 & ":C#Local:O#Top:U2#INNER:UD3#REGIONAL_RPT + A#423100:E#" & strEntTx3 & ":C#Local:O#Top:U2#INNER:UD3#REGIONAL_RPT + A#411000:E#" & strEntTx3 & ":C#Local:O#Top:U2#INNER:UD3#REGIONAL_RPT)")
api.Data.FormulaVariables.SetDataBufferVariable("rptValue",regionalRpt,False)
api.Data.Calculate("A#P_EBIPrct:O#Import:U2#P0000:UD3#REG_ADJ:U4#PJ00000 = Divide($rptValue,A#411000:E#"& strEntTx3 &":C#Local:O#Top:U2#INNER:UD3#REGIONAL_RPT) - Divide($DBCInner, A#411000:E#" & strEntTx3 & ":C#Local:O#Top:U2#INNER:UD3#COUNTRY_RPT)")

I would appreciate your assistance.

Finance
System

1 Reply

  • BenEppel's avatar
    BenEppel
    New Contributor III
    24 days ago
        Dim startingBuffer2m1 As String = "Cb#XXXXXXX:T#" & TimePrior & ":A#" _
        & Denominator & ":S#Actual:E#XXXXXXXX:F#EndBal_Input:V#Periodic"
    Dim startingBuffer2m2 As String = "Cb#XXXXXXX:T#" & TimePrior2 & ":A#" _
        & Denominator & ":S#Actual:E#XXXXXXXX:F#EndBal_Input:V#Periodic:"
    Dim startingBuffer2m3 As String = "Cb#XXXXXX:T#" & TimePrior3 & ":A#" _
        & Denominator & ":S#Actual:E#XXXXXX:F#EndBal_Input:V#Periodic:"
    Dim Denominator1 As DataBuffer = api.Data.GetDataBufferUsingFormula(
        "RemoveZeros(FilterMembers(" & startingBuffer2m1 & ", " & filter1 & ", " & filter2 _
        & "))",, True)
    Dim Denominator2 As DataBuffer = api.Data.GetDataBufferUsingFormula(
        "RemoveZeros(FilterMembers(" & startingBuffer2m2 & ", " & filter1 & ", " & filter2 _
        & "))",, True)
    Dim Denominator3 As DataBuffer = api.Data.GetDataBufferUsingFormula(
        "RemoveZeros(FilterMembers(" & startingBuffer2m3 & ", " & filter1 & ", " & filter2 _
        & "))",, True)
    api.Data.FormulaVariables.SetDataBufferVariable("Denominator1", Denominator1, False)
    api.Data.FormulaVariables.SetDataBufferVariable("Denominator2", Denominator2, False)
    api.Data.FormulaVariables.SetDataBufferVariable("Denominator3", Denominator3, False)

    Dim db_DenominatorAvg As New DataBuffer
    db_DenominatorAvg = api.Data.GetDataBufferUsingFormula(
        "($Denominator1+$Denominator2+$Denominator3)/3")

    Break out the formula variables by account and then add them together before dividing like db_DenominatorAvg above.