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:
I get blank rows with or without the sum calculation in the variable.
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 "EUR-Amount" 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.
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.
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?