Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
nleitgeb
Contributor II
Contributor II

Dynamic fieldname in expression for currency conversion

 

Hi everybody,

 

for a dynamic currency conversion in QlikView I like to use dynamic fieldnames in an expression.  Unfortunately without success and I need some help please!

 

 

Here is an example:

 

The bold part is the dynamic expression.

 

 

I`ve tried to generate this part without success.

 

 

 

What`s wrong?

 

There are about 15 currencies and 8 KPI`s, thus I don`t want to create these conversions in script.

 

Thanks in advance!

 

BR,

Nicole

 

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

In that case I think you'll have to do it like this and list all currencies and rate fields:

[Gross Sales] /

pick(Match([Currency Invoice], 'GBP', 'USD'), ExRateGBP, ExRateUSD)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

View solution in original post

6 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Are the rates constant for the data or do they vary by invoice?

-Rob

nleitgeb
Contributor II
Contributor II
Author

The rates vary by invoice!

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

In that case I think you'll have to do it like this and list all currencies and rate fields:

[Gross Sales] /

pick(Match([Currency Invoice], 'GBP', 'USD'), ExRateGBP, ExRateUSD)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

nleitgeb
Contributor II
Contributor II
Author

It`s working! Thank you very much, Rob!!!

BR,

Nicole

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Good to hear. For performance you may want to do the pick in the script and establish a new field:

pick(Match([Currency Invoice], 'GBP', 'USD'), ExRateGBP, ExRateUSD) as Rate


and then your chart expression is simplified to:


[Gross Sales] / Rate


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

nleitgeb
Contributor II
Contributor II
Author

Thank you very much! It´s working as well