Dynamic fieldname in expression for currency conversion

[nleitgeb]

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

[rwunderlich]

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

-Rob

[nleitgeb]

The rates vary by invoice!

[rwunderlich · Accepted solution]

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]

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

BR,

Nicole

[rwunderlich]

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]

Thank you very much! It´s working as well