Multiple conversion rates

[Anonymous]

Dear community,

I am trying to get the value of a budget (plus variations based on which FX Rate...) in different currencies depending of the local currency of each entity.

I have for example this fact table:

Entity	Year	Department	Amount	Type	Local Currency	HQ Currency	WW HQ Currency
A	2014	HR	1000	Forecast	GBP	EUR	USD
B	2013	Finance	150	Actual	KRW	KRW	USD
C	2014	IT	250	AOP	EUR	EUR	USD
D	2014	HR	1600	Actual	CZK	RUB	USD

Then I have different conversion rates:

FXYear	FXMonth	BudgetType	CurrencyFrom	CurrencyTo	Rate
2013	1	Actual	EUR	USD	1.3
2014	All	AOP	KRW	KRW	1
2014	1	Actual	EUR	USD	1.4
2014	2	Forecast	GBP	EUR	.8

From that, I would like to get something like that based on

Cat	Amount (USD)
Finance	.123
IT	390
HR	1100

As I would like to select if I want it in the Local, HQ, or WW currency and depending if I select the AOP/Actual or forecast rate for the current year, I was planning to do as follow:

sum(Amount*only({1<FXYear = {$(=Year)}, FXMonth={$(=if(Type='AOP','All', Month))}, BudgetType={$(=vCurrType)}, CurrencyFrom={$(=LocalCurrency)}, CurrencyTo={$(=HQCurrency)} >} Rate))

I am aware a set analysis is not the best idea, I was wandering if a peek function for expressions were existing. Basically, I want to return a value from the rates table depending on multiple criteria coming from a selection in my qvw and from fact table (but I can't allocate the FXRates table to a fact table line as I have to let the user to select which type of rate he wants to use.

Don't hesitate to ask me if I am not clear enough. It seems clear in my head but maybe not here.

Many thanks in advance for your help.

[Oleg_Troyansky]

My recommendation would be to solve the problem in the load script and to store the relevant Rate within the fact table. The extra memory used for it, will be well justified by the savings in the run-time calculations

cheers,

Oleg Troyansky

www.masterssummit.com

[Anonymous]

Hi Oleg and thank you for your answer!

I would have done that if I had not to let the possibility to the end-user to choose himself which Type (AOP/Forecast/Actual) conversion rate we would like to apply. Furthermore I need to add a variance analysis based both on value and FX Rates.

[Oleg_Troyansky]

Jonathan,

In this case, I'd load all three types (AOP/Forecast/Actual) as three fields in the Fact and use the corresponding value in the expression, based on the user selection.You can faciliate it in a number of ways - through the use of conditional expressions or using variables that hold the corresponding expression formulas.

[Anonymous]

Oleg,

I thought a lot about that but how to deal with multiple steps (Local -> HQ -> WW) conversions. Should I have 6 columns 3 for each conversions?

Maybe should I use a key and I would be able to link the FX rates table with each line and try like in this example? FX / Currency translation / consolidation using... | Qlik Community

Here is the solution I've found:

First, create a table with the expressions:

FX_Type:

LOAD * INLINE [

    FX_Type, FXCal

    AOP, Num(RateAOP/RateAOPFinal)

    Act, Num(RateAct/RateActFinal)

    Fcst, Num(RateFcst/RateFcstFinal)

];

Then as the link before create a mapping with the Entity and the currency.

[Data Reporting]:

LOAD Entity,

  AutoNumber(Year&'-'&Month&'-'&ApplyMap('CurrEntity',Entity)) as KeyConversion, // Link with the Rate table

  ApplyMap('CurrEntity',Entity) as Currency, // Put the Local Currency

  AutoNumber(Year&'-'&Month) as KeyPeriod, // Link with the desired Currency Table

  Month,

  Year,

  Amount,

  Type // If Act, Fcst of AOP

FROM xxxxx;

Finally, load the last two tables very similar:

FX_Rates1:

LOAD Autonumber(Year&'-'& Month&'-'&Currency) as KeyConversion, // Link with the Data Reporting Table

     RateAct,

     RateAOP,

     RateFcst

FROM zzzzz;

Load a second table with the same set of Data:

FX_Rates_Final:

LOAD AutoNumber(Year&'-'&Month) as KeyPeriod, // Link with the Data Reporting

  Currency as FinalCurrency, // Currency chosen to display

  RateAct as RateActFinal,

  RateAOP as RateAOPFinal,

  RateFcst as RateFcstFinal

FROM zzzzz;

That's for the script.

In the design I have 2 list Box : FinalCurrency and FX_Type (I chose to put a alternate State name for this last one)

and a pivot table with the expression "sum(Amount*$(=only({CurrReport} FXCal)))"

Many thanks Oleg for having driven me on the right path!