Set modifier element also a dimension - for inferring FX rates

sarahallen1 — Wed, 15 Feb 2012 15:57:13 GMT

I have some data giving FX (foreign exchange, i.e. currency) rates as GBP/unit or unit/GBP - the only base currency I have for my data is GBP. However, from this it is easily inferred what the FX rates are between 2 non-GBP currencies, as the ratios are simple multiples of one another.

E.g. in my data I have rates for USD/GBP, GBP/USD, EUR/GBP and GBP/EUR. I can easily work out (in a spreadsheet - or just a calculator!) what USD/EUR is, for example, but I can't do this in the QlikView table as I want. The calculation (example) is:

USD/EUR = (USD/GBP) / (EUR/GBP) .

I have attached an Excel file showing how these rates are inferred in practice.

The GBP/USD style rates are the field name [Local Cost per Unit] in my QVW (attached), and the USD/GBP style rates are [Base Cost per Unit]. I have a single value, GBP, for the field [Base Currency Code]. I have 8 values (AUD, CAD, CHF, EUR, GBP, USD) for the field [Local Currency Code].

I have duplicated the field [Local Currency Code] as an island field named [ISLAND Local Currency Code] so that this field always has the same 8 values as [Local Currency Code]. The intention is that I can then pick any on of these 8 currencies from the island field, and use set analysis to convert rates so that this is the new effect base currency.

I have a pivot table with dates down the left as one dimension, and [Local Currency Code] across the top as another. The expression I am trying to use is:

= only([Local Cost per Unit])

//For the currency in that column as per the dimension

/ only({$<[Local Currency Code]={$(=[ISLAND Local Currency Code])}>}[Local Cost per Unit])

//For the currency selected in the ISLAND (the specified base currency)

This is only working in the column where the [Local Currency Code] across the top dimension is the one I have picked in the island as my new base currency. I think this is because the set modifier in the denominator of my expression is asking the [Local Currency Code] to be something different than it is according to that column of the dimension.

However, this seems (to me) to be a very natural / simple thing to do with my data set. I would really appreciate it if anyone can help!

Thanks,
Sarah

Re: Set modifier element also a dimension - for inferring FX rates

lironbaram — Thu, 16 Feb 2012 07:04:13 GMT

hi shara

check the attach file , i only changed your formula to

only([Local Cost per Unit])
//For the currency in that column as per the dimension

/

only({$<
[Local Currency Code]={$(=[ISLAND Local Currency Code]) } //For the currency selected in the ISLAND (the specified base currency)
>}total<[As of Date]> [Local Cost per Unit])

i added the total to the second part other wise it will only work for the selected currency

Re: Set modifier element also a dimension - for inferring FX rates

sarahallen1 — Thu, 16 Feb 2012 08:54:17 GMT

This is perfect - thank you very much Liron! The table is correct now.

Also useful to see the total<[As of Date]> structure in action; hopefully I will be able to use it elsewhere now too.

Thanks again,

Sarah

topic Re: Set modifier element also a dimension - for inferring FX rates in QlikView

Set modifier element also a dimension - for inferring FX rates

Re: Set modifier element also a dimension - for inferring FX rates

Re: Set modifier element also a dimension - for inferring FX rates