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.
