topic Currency Conversion Expression in QlikView

Currency Conversion Expression

Tue, 16 Aug 2011 17:34:19 GMT

Hi,

I have a currency conversion expression as per below:

sum({<[Version]={'ACTUAL'},[Location Level 2]={'OA'}>} IF([FX Code From]='USD',_Amount, IF([FX Code To]='ZAR',_Amount / 6.92,_Amount / [FX Rate])))

The problem is to replace the 6.92 dynamically with the [FX Code To] = USD, FX Rate in order to use the current exchange rate.

The expression works "perfectly" except for the case that the rate for that condition is hard coded.

How do I modify the expression to use the "USD FX Code To" rate?

Do you have any idea what the syntax should look like to replace the 6.92? Something like _Amount / {<[FX Code To]=’USD’>} [FX Rate] ?

I'd appreciate any help or suggestions

Thanks in advance.

Regards,

Werner

Currency Conversion Expression

swuehl — Tue, 16 Aug 2011 18:05:14 GMT

Werner,

the best solution might bedepending on your data model.

If the Exchange rate data is associated to the field [FX Code From], you could maybe use a simpler expression like

=sum({<[Version]={'ACTUAL'},[Location Level 2]={'OA'}>} _Amount / [FX Rate])

but anyway, a change to

_Amount / only({<[FX Code To]=’USD’>} [FX Rate])

should / could work to use a dynamic exchange rate.

Regards,

Stefan

Currency Conversion Expression

Tue, 16 Aug 2011 19:53:53 GMT

Thank you Stefan, I really appreciate your help and advice. I'm new to QlikView, set analysis etc...I have to start supporting an existing QlikView model.

I’m getting a “error in expression” when I use the expression as per below:

sum({<[Version]={'ACTUAL'},[Location Level 2]={'OA'}>}

IF([FX Code From]='USD',_Amount,

IF([FX Code To]='ZAR',_Amount / Only({<[FX Code To]=’USD’>} [FX Rate]),_Amount / [FX Rate])))

Is there something else I have to add to the only statement?

Thanks again.

Regards,

Werner

Currency Conversion Expression

swuehl — Tue, 16 Aug 2011 20:28:56 GMT

Ah yes, of course, my fault.

It is not allowed to use a aggregation function inside an aggregation without using the TOTAL qualifier.

Not sure if you want this qualifier here (it will disregard the charts dimension).

Could you put the [FX Code To]=’USD’ inside the first set expression? Or don't you want to convert to USD in all cases?

If you could post your data model (or an relevant part of it ) or a sample app here (upload available in advanced editor), this may make help a little easier.

Regards,

Stefan