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

    Set modifier element also a dimension - for inferring FX rates

    Sarah Hymers

      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!