topic Re: Working with set analysis with KPI in App Development

Working with set analysis with KPI

Anonymous — Sun, 17 Dec 2017 05:30:02 GMT

I would like to set my KPI to be based on the latest year when users open up the worksheet. This will have a secondary figure which shows the value of year-1. However, I would like the figure in KPI to change based on user selection. For example it is now 2017. The sales figure for the sales field should sum up 2017 and show 2016 figure for comparison. If users select 2016 in the filter, the KPI should show 2016 and 2015 figures. How can accomplish this?

I am starting with this.

sum( {$<Year = {$(=Only(Year)-1)}>} Sales )

sum( {$<Year = {$(=Only(Year))}>} Sales )

Re: Working with set analysis with KPI

kaushiknsolanki — Sun, 17 Dec 2017 05:48:15 GMT

This should also work, but if not then try changing Only function to Max.

sum( {$<Year = {$(=Max(Year)-1)}>} Sales )

Regards,

Kaushik Solanki

Re: Working with set analysis with KPI

Anonymous — Sun, 17 Dec 2017 05:53:23 GMT

Based on my understanding $ means current selection? hence max of the current selection? If I use 1 instead of $, then MAXi is returned based on MAX of the entire data set. Am I correct?

Re: Working with set analysis with KPI

kaushiknsolanki — Sun, 17 Dec 2017 06:11:54 GMT

Yes,

You are right, When you use 1 it wont consider any selections. So in your case if anyone selects 2016 then also it will show 2017 and 2016 as previous year.

So to satisfy your requirement you should use $.

Regards,

Kaushik Solanki

Re: Working with set analysis with KPI

Anonymous — Sun, 17 Dec 2017 06:47:54 GMT

Kaustik

Thanks for confirming. By default, how does Qlik knows what was selected when users first open up the worksheet?

Re: Working with set analysis with KPI

kaushiknsolanki — Sun, 17 Dec 2017 06:50:52 GMT

Qlik doesnt know default value when nothing is selected and thus we write the set analysis to tell Qlik to consider value.

In your case if you use the Max function, it will take the maximum value from the entire data and assign it as default value, but as soon as selection is made it will recalculate the max and assign it as default value.

Regards,

Kaushik Solanki

Re: Working with set analysis with KPI

Anonymous — Sun, 17 Dec 2017 09:26:49 GMT

Kaushik

Please bear with me. It's my first 4 days using Qlik sense. I applied the codes in the KPI. If I had selected 2017, I expected 16 to be the defect found and 38.1k to be the previous year.

Yes both numbers remained the same. Any idea what I did wrong?

Re: Working with set analysis with KPI

kaushiknsolanki — Sun, 17 Dec 2017 10:24:42 GMT

It is because the field name you used in set analysis is wrong, it should be Date.Year, because that is the actual name of field.

So change the Year to Date.Year in your set analysis.

Regards,

Kaushik Solanki

Re: Working with set analysis with KPI

Anonymous — Sun, 17 Dec 2017 10:50:15 GMT

I've tried

sum( {$<'Date.Year' = {$(=Max('Date.Year'))}>} [Defective Qty] )

and

sum( {$<Date.Year = {$(=Max(Date.Year))}>} [Defective Qty] )

Both return the entire set total.