Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
Showing results for 
Search instead for 
Did you mean: 

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