topic Re: Dynamic fieldname in expression for currency conversion in QlikView

Dynamic fieldname in expression for currency conversion

nleitgeb — Wed, 26 Apr 2017 08:03:48 GMT

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

Re: Dynamic fieldname in expression for currency conversion

rwunderlich — Wed, 26 Apr 2017 10:46:36 GMT

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

-Rob

Re: Dynamic fieldname in expression for currency conversion

nleitgeb — Wed, 26 Apr 2017 10:48:22 GMT

The rates vary by invoice!

Re: Dynamic fieldname in expression for currency conversion

rwunderlich — Wed, 26 Apr 2017 11:00:59 GMT

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

Re: Dynamic fieldname in expression for currency conversion

nleitgeb — Wed, 26 Apr 2017 11:13:12 GMT

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

BR,

Nicole

Re: Dynamic fieldname in expression for currency conversion

rwunderlich — Wed, 26 Apr 2017 11:25:46 GMT

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

Re: Dynamic fieldname in expression for currency conversion

nleitgeb — Wed, 26 Apr 2017 13:09:36 GMT

Thank you very much! It´s working as well