Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I am using Qlik Sense and trying to create two KPI's.
One KPI is for the latest month results and another KPI is for the month results that is before the latest.
I can create KPI for the latest month, but I am struggling to create one that is before the latest.
This is the expression I am using to get the latest KPI:
sum( {$<Sale_Service={[Service messages],[Sales messages]}, year_month = {'$(=max (year_month))'}>} Active_Count )
However by using above expression I cannot change between months, meaning if I use filter and choose different months it won't give me result for those months, it will just stay and show latest month.
This is the expression I used, so KPI can change once I apply filters (unfortunately, it didn't work)
=sum({<year_month={$(=if(GetSelectedCount(year_month)<=1,chr(39)&max (year_month)&chr(39),chr(39)&GetFieldSelections(year_month,chr(39)&chr(44)&chr(39))&chr(39)))}>} Active_Count )
Ideally, I would need expression, that would show me:
Any help is appreciated.
Hi James,
I have played around with something and it seems to work in my Desktop April 2019.
For current Month:
=sum( {$<Sale_Service={[Service messages],[Sales messages]}>*< year_month = {'$(=max (year_month))'}>} Active_Count )
For prior Month:
=sum( {$<Sale_Service={[Service messages],[Sales messages]}>*< year_month = {'$(=Addmonths(max (year_month)), -1)'}>} Active_Count )
If you YearMonth is formatted as 201905 then you will need to change it from using AddMonths to something like max(year_month) - 1. With some considerations for Jan.
Let me know if it works!
Thanks
Thank you for your reply, much appreciated!
Current version is Qlik Sense Februrary 2019 Patch 2, maybe that's the case..
I tried your expression for current month it didn't work.
and also for the prior month.
I have attached image how it looks month values in the data tab, maybe this would help:
Could you upload a sample QVF? Unsure why it isn't behaving as expected. Maybe a more experienced user could point out where I am wrong.
The addmonths() would need to change to year_month - 1 or -89 if Max(year_month) was in Jan.
Thanks
I can't unfortunately do that.
Perhaps I could make screenshots and you can tell me what exactly?
Regards