Working with set analysis with KPI

[Anonymous]

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 )

[kaushiknsolanki]

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

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

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

Regards,

Kaushik Solanki

[Anonymous]

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?

[kaushiknsolanki]

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

[Anonymous]

Kaustik

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

[kaushiknsolanki]

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

[Anonymous]

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?

[kaushiknsolanki]

Hi

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

[Anonymous]

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.