FX / Currency translation / consolidation using QlikView

Anonymous — Wed, 27 Oct 2010 09:09:27 GMT

Hi,

I have a fairly complex set of data and a requirement to deliver functionality for showing underlying numbers in selected currencies.

Today we have a consolidation system doing this job, but it would be fantastic to develop something similar in QlikView.

Basically what we have is:

50 different companies all with the financial numbers in local currencies, let us call it LOC with their own currency CMP_CUR

50 different currencies, e.g. USD, SEK, EUR call it CUR

1 rate for each currency for each month (vs USD) so e.g. 1, 7.0 0.8

So what I want to achive is that if I have a LOC in EUR of 100, I want to be able to show this as 875 SEK (100/0,8*7) or 125 USD (100/0,8)

So something similar to:

=SUM(LOC/IF(CMP_CUR=CUR,Rate,1)*(IF(SelectCur=CUR,Rate,1))

To add to the complexity the rate of course is dependent on which period the underlying data comes from.

So something similar to:

=SUM(LOC / IF(CMP_CUR=Cur and DataPer=CUR_Per,Rate,1)*IF(SelectCur=CUR and DataPer=CUR_Per,Rate,1)

And of course I want to have Actuals in one column at Actuals rate and Budget in the other column at Budget rates...

Any one who has been able to create somthing like this?

If I look at e.g. "What's New in QV9" they have taken the short cut to have data structured like this:

Company | Period | Account | SEK | USD | GBP | EUR

But that then means that I need to have all the amounts precalculated before loading in to QlikView.

Any ideas or experience sharing would be highly appreciated.

Kind regards

Niklas

Wed, 27 Oct 2010 09:43:39 GMT

Hi, This is a way

Regards

Benoît

Anonymous — Wed, 27 Oct 2010 13:22:46 GMT

Fantastic!

This exactly solved my problem.

Perfornance vice it is extremely quick as well and by setting up the currency keys in a correct way it works al budgets, actuals, forecast etc.

Could not be happier!

Thanks a lot.

Niklas

Re: FX / Currency translation / consolidation using QlikView

Wed, 03 Apr 2013 21:13:53 GMT

It's not PE enables. Can you post the code here?

Thu, 11 Apr 2013 06:44:39 GMT

CAn you please share PE enabled file or code?