Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Calculating with different Currency rates (sum in pivot)

Hello comunity,

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:

=sum(aggr(sum($(vEurAmount))

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.

thank you.

5 Replies
Partner
Partner

Re: Calculating with different Currency rates (sum in pivot)

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

Not applicable

Re: Calculating with different Currency rates (sum in pivot)

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.

Partner
Partner

Re: Calculating with different Currency rates (sum in pivot)

Hi

Can you please share your Demo file so that its more clear, Will help you.

Regards,

Nirav Bhimani

Highlighted
Not applicable

Re: Calculating with different Currency rates (sum in pivot)

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:

qlik.png

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.

Not applicable

Re: Calculating with different Currency rates (sum in pivot)

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.