2 Replies Latest reply: Feb 16, 2012 3:54 AM by Sarah Hymers

Set modifier element also a dimension - for inferring FX rates

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
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

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