
Re: Calculating with different Currency rates (sum in pivot)
Nirav Bhimani Mar 7, 2013 5:28 AM (in response to Thorsten Schröder)HI,
Use Not Wildmatch.
if( not Wildmatch(Currency,'EUR'), sum(Amount / (1 * ECBrate)),sum(Amount))
When you use aggr fn then you should mention the field name to do the group by.
=sum(aggr(sum($(vEurAmount)),FieldName))
Regards,
Nirav Bhimani

Re: Calculating with different Currency rates (sum in pivot)
Thorsten Schröder Mar 7, 2013 6:03 AM (in response to Nirav Bhimani )thanks for the expressions.
I still have the problem with my variable:
when I write
=sum(aggr(sum(if(not Wildmatch(Currency,'EUR'), Amount / (1 * ECBrate),Amount)),Currency))
in my pivot expression, the right sum is calcluated.
How do I need to modify my variable
vEurAmount to use the expression you posted:
=sum(aggr(sum($(vEurAmount)),Currency))
I get blank rows with or without the sum calculation in the variable.

Re: Calculating with different Currency rates (sum in pivot)
Nirav Bhimani Mar 7, 2013 6:53 AM (in response to Thorsten Schröder)Hi
Can you please share your Demo file so that its more clear, Will help you.
Regards,
Nirav Bhimani

Re: Calculating with different Currency rates (sum in pivot)
Thorsten Schröder Mar 7, 2013 8:53 AM (in response to Nirav Bhimani )I'm working on real data so I can't create a file for it. BTW I changed the Currency field to InvoiceCurrency.
Let me try to explain it with this picture:
In the "EURAmount" pivot table I placed above expression which delivers the correct result for the total.
But since I need this expression in various ways, I need it in a variable.
In the "Point in Time Reporting" for example. The "Sales YTD" doesn't deliver the desired result. But I wasn't able to copy&paste the working expression above to replace the $(vEurAmount) statement.
So I believe the variable expression needs to be changed.




Re: Calculating with different Currency rates (sum in pivot)
Thorsten Schröder Mar 18, 2013 4:17 AM (in response to Thorsten Schröder)Since I can't get it to work like that I would like to create a new calculated field in my script for the EUR Amount.
this statement:
if(NOT Wildmatch(InvoiceCurrency,'EUR'), (Amount / (1 * ECBrate)),Amount) AS EUR_Amount
should create the new field. Problem I have is that the ECBrate field comes from a different table.
Both tables share a key field "CombCurrDate".
What do I need to do to get this calculation running?
thank you.