Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi, This is a way
Regards
Benoît
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
It's not PE enables. Can you post the code here?
CAn you please share PE enabled file or code?