Set modifier element also a dimension - for inferring FX rates

[sarahallen1]

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

[lironbaram · Accepted solution]

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

[sarahallen1]

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