Offset currency changes based on Year 1 FX rate.

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

topic Offset currency changes based on Year 1 FX rate. in QlikView

Offset currency changes based on Year 1 FX rate.