4 Replies Latest reply: Feb 25, 2016 9:24 AM by Marcus Sommer RSS

    Same expression gives different results being placed in Dimension and Expression

    Li Ki

      Dear Community,

       

      I have encountered a strange and magic problem. I'm creating a customer debts report. The overall debts amount is calculated as where

      [End_Date] >= [C.Due Date]


      So the expression for calculating the overall debt is defined as

      =Sum ( if( [End_Date] >= [C.Due Date], 1, 0 )

      * [C.Amount] 

      * if( ([C.Proforma_flag] = 1 OR [C.Prepayment_flag] = 1), 0, 1 )))

       

      BUT. I'm not having the right numbers in the Pivot Table.

      1.JPG

      (The Due Amount + Prepayment should give the Sum, EUR (be 822), but it is not.)

       

      But when I move the expression of debts identification to the calculated dimension:

      =if( [End_Date] >= [C.Due Date], 1, 0)      (AS Due)

      and leave another part in the expression:

      =sum( [C.Amount]

      * if( ([C.Proforma_flag] = 1 OR [C.Prepayment_flag] = 1), 0, 1 ))

      then I get the right number.

      2.JPG

       

      What can it be? Why can this happen? Please help...

       

      Thanks in advance!