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

# Same expression gives different results being placed in Dimension and Expression

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.

(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.

• ###### Re: Same expression gives different results being placed in Dimension and Expression

Try this:

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

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

or

=Sum ({< [End_Date] = {">=[C.Due Date]"},

([C.Proforma_flag] = {1} + [C.Prepayment_flag] = {1})>} [C.Amount])

- Marcus

• ###### Re: Same expression gives different results being placed in Dimension and Expression

Thank you for the quick respond. No, this doesn't work... I have already tried billion of different ways in Set Analysis and with if's. When I use IF's the result is always as in the picture. When using set analysis it is always 0.

• ###### Re: Same expression gives different results being placed in Dimension and Expression

Maybe there is something wrong with a data model... This is the part of it:

[%.C.ID]:

Autonumber(

[Comp] & ' | ' &

[Entry No_] & ' | ' &

[Transaction No_] & ' | ' &

[Posting Date] & ' | ' &

'Customer') AS [%.C.ID]

End_Date tries to reach the Due date through a LinkTable, maybe it is difficult for it?

• ###### Re: Same expression gives different results being placed in Dimension and Expression

It's difficult to say if this is the reason - it will be depend on the available keys if it could work - but I would seriously consider to change the datamodel.

Another important part related to set analysis is that fields must have exactly the same format, for example by month 2 wouldn't match to Feb and by date-fields it's the same.

- Marcus