4 Replies Latest reply: Feb 25, 2014 9:20 AM by Jonathan Poyer RSS

    Multiple conversion rates

    Jonathan Poyer

      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:

      EntityYearDepartmentAmountTypeLocal CurrencyHQ CurrencyWW HQ Currency
      A2014HR1000ForecastGBPEURUSD
      B2013Finance150ActualKRWKRWUSD
      C2014IT250AOPEUREURUSD
      D2014HR1600ActualCZKRUBUSD

       

      Then I have different conversion rates:

      FXYearFXMonthBudgetTypeCurrencyFromCurrencyToRate
      20131ActualEURUSD1.3
      2014AllAOPKRWKRW1
      20141ActualEURUSD1.4
      20142ForecastGBPEUR.8

       

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

      CatAmount (USD)
      Finance.123
      IT390
      HR1100

       

      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.

        • Re: Multiple conversion rates
          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

            • Re: Multiple conversion rates
              Jonathan Poyer

              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.

                • Re: Multiple conversion 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.

                    • Re: Multiple conversion rates
                      Jonathan Poyer

                      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!