topic Re: KPI expression for current and last month comparision in Qlik Sense in App Development

KPI expression for current and last month comparision in Qlik Sense

jamesjames — Tue, 28 May 2019 08:53:24 GMT

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.

Re: KPI expression for current and last month comparision in Qlik Sense

LReeve — Tue, 28 May 2019 09:36:00 GMT

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

Re: KPI expression for current and last month comparision in Qlik Sense

jamesjames — Tue, 28 May 2019 10:28:01 GMT

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:

Re: KPI expression for current and last month comparision in Qlik Sense

LReeve — Tue, 28 May 2019 10:38:26 GMT

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

Re: KPI expression for current and last month comparision in Qlik Sense

jamesjames — Tue, 28 May 2019 11:49:46 GMT

I can't unfortunately do that.

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

Regards

Re: KPI expression for current and last month comparision in Qlik Sense

LReeve — Tue, 28 May 2019 13:25:08 GMT

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

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