9 Replies Latest reply: Apr 4, 2016 12:24 AM by Swetha More RSS

    Dynamic Calculation of Exchange rate

    Swetha More

      Hi Experts,

       

      I have a pivot table which looks like as shown in the attachment. Basically it shows the sales of Current month of current year which is "CY" and the sales of Current month in the previous year which is "PY". The user can select the exchange rate date in the filter pane "Exchange Rate Date" which could be for example Jan 2015 and Jan 2016. Therefore in the Exchange Rate CY column it should display the Jan 2016 Exchange rate and similarly in Exchange Rate PY column the Jan 2015 Exchange rate should be displayed.

       

      The exchanges rates are stored on monthly basis in a database table, the data in Db table looks like as shown in the attachment "Exchange Rate Table". All that has to be done is match the date that is chosen in the Exchange Rate Date with the date in the "name" field in the db table and display the corresponding exchange rate in the pivot table for that month. The Exchange Rate Date filter plane field is also the "name" field itself.

       

      Its a simple expression but i am not able to get the correct values. Can someone guide with the chart expression that can be used here.

       

      Will set analysis work? or I have to use simple if condition?

       

      Note: I am using Qliksense.

       

      Thanks

      Swetha