Dynamic Calculation of Exchange rate

[#1]

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

[sunny_talwar]

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

[#3]

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

[sunny_talwar]

Can you try this:

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

and

Min(TOTAL name)

[#5]

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

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

Thanks

Swetha

[perumal_41]

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

[perumal_41]

try this one

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

[#8]

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

[reddy-s]

Hi Swetha,

If possible, please share a sample QVF file.

Thanks,

Sangram.

[#10]

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