Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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