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

# Dynamic Calculation of Exchange rate

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

• ###### Re: Dynamic Calculation of Exchange rate

What is your expression you are using for Exchange rate? May be you need to add TOTAL Qualifier to get it to work.

• ###### Re: Dynamic Calculation of Exchange rate

Exchange Rate Expression is as simple as if(name = max(name),rate) for current year and min(name) for previous year.

• ###### Re: Dynamic Calculation of Exchange rate

Can you try this:

Only(TOTAL If(name = Max(name), rate))

and

Min(TOTAL name)

• ###### Re: Dynamic Calculation of Exchange rate

Hi.  I tried this but unfortunately doesn't work..

Only({total <[name]={'=\$(=MonthStart(\$(CurrMonth)))'}>rate}

Thanks

Swetha

• ###### Re: Dynamic Calculation of Exchange rate

is possible to upload sample application. so easily can understand your problem and easily help to you

• ###### Re: Dynamic Calculation of Exchange rate

try this one

Only({total <[name]={'=\$(=MonthStart(\$(CurrMonth)))'}>} rate)

• ###### Re: Dynamic Calculation of Exchange rate

Hi.....

This doesnt work Only({total <[name]={'=\$(=MonthStart(\$(CurrMonth)))'}>} rate). I also tried this but in vain - Only({total<{(inmonth(max(name),\$(CurrMonth),0)={'-1'}}>rate}).

Thanks

Swetha

• ###### Re: Dynamic Calculation of Exchange rate

Thanks everyone --- Finally this expression worked - Only({<[name]={'>=\$(=MonthStart(\$(CurrMonth)))<=\$(=MonthEnd(\$(CurrMonth)))'}>}rate)

• ###### Re: Dynamic Calculation of Exchange rate

Hi Swetha,

If possible, please share a sample QVF file.

Thanks,

Sangram.