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: 
jamesjames
Contributor II
Contributor II

KPI expression for current and last month comparision in Qlik Sense

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:

  • KPI-1, Latest month results (that can change, if I click on the filter panel and choose different months)
  • KPI-2, One before the latest month results (also changes if I choose specific filters)

 

Any help is appreciated.

5 Replies
LReeve
Contributor III
Contributor III

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

jamesjames
Contributor II
Contributor II
Author

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:

 

months.PNG

 

 

LReeve
Contributor III
Contributor III

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

jamesjames
Contributor II
Contributor II
Author

I can't unfortunately do that.

 

Perhaps I could make screenshots and you can tell me what exactly?


Regards

LReeve
Contributor III
Contributor III

How about a sample data file with a small amount of fake data?

I can't quite work out what's wrong!