Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
omarberg
New 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.