Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
omarberg
Contributor
Contributor

Offset currency changes based on Year 1 FX rate.

I have been trying to calculate an offset in sales caused by currency change in Branch Local currency.

The group currency is EUR and i have FXchange table that has FX rates for EUR to Local currency for 15 currencies.

I have been successful to calculate this for each of the currencies but when I want to display all of them (currency as dimension) then it goes wrong.

So To calculate the the offset I use the following formula where I am want to Convert the Group currency value to local currency and then convert it back to Group currency based on the First Fiscal year selecte:

= Sum([Sales Group Currency])

-

Sum([Sales Group Currency])   *  (avg(FXRate) /   avg({$ <  [FXYEAR] = {"$(=min([Fiscal Year]))"}> } FXRate)

This doesn't work as the "avg({$ <  [FXYEAR] = {"$(=min([Fiscal Year]))"}> } FXRate" is calculated only for the first year but i want to use it to divide  all years. So basically how can I calculate a FX conversion rate for all years based on the FXRate of the first year?


I have tried to put this into a variable and then the calculation work for one selected currency but not when I add more currencies to the graph as then the variable is averaging FX rates of all the currencies.

= Sum([Sales Group Currency])

-

Sum([Sales Group Currency])   *  (avg(FXRate) /   vFXminYear)


I have only been using QlikView for a month.


All suggestions are appreciated.

0 Replies