Discussion Board for collaboration related to Creating Analytics for QlikView.
I need help with my currency problem:
I have various currencies and I use a currency/rate table (ECB) to get the right values at the right day.
I now need to multiply my Amount field which also has a Curreny field with the right Currency and Date. This all works fine.
The problem I have is that when my currency is EUR I don't have any values in my ECB rate table, so I'll just use this if statement in my vEurAmount variable:
if(Currency <>'EUR', sum(Amount / (1 * ECBrate)),sum(Amount))
In my straight table I can set the total mode to "Sum of all rows" but I can't use this in my pivot table.
So I tried this expression:
which won't return anything.
I've tried other combinations but I can't get the syntax right.
Hope you can help me to set up the right expression.
BTW, is the variable I defined the right way to do this? I've also removed the sum function in the variable but no luck either.
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.
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:
vEurAmount to use the expression you posted:
I get blank rows with or without the sum calculation in the variable.
Can you please share your Demo file so that its more clear, Will help you.
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?