Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

9 Replies
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.

Not applicable
Author

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)

Not applicable
Author

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

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

Thanks

Swetha

perumal_41
Partner - Specialist II
Partner - Specialist II

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

perumal_41
Partner - Specialist II
Partner - Specialist II

try this one

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

Not applicable
Author

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
Master II
Master II

Hi Swetha,

If possible, please share a sample QVF file.

Thanks,

Sangram.

Not applicable
Author

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