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

Qlik Sense Filters Selections Jumping/Skipping Scnearios (Example: Years Filter)

Hello Experts,

 

I am trying to achieve a functionality in Qlik Sense Sheet. i have a year Filter Pane in a sheet (Year-2015,2016,2017,2018), if a user select Jumping/Skipping a year (Selections Example -2015,2017,2018..Skipping 2016 OR Selections Example -2015,2018..Skipping 2016,2017 IT SHOULD SHOW ERROR),

My CHART should show error, we can achieve the error message if nothing is selected using the if condition with Something like ""If(len(GetFieldSelections(Year))=0,'Error')"" but wondering how we can deal with the Jumping/Skipping Scenarios like above.

 

Thanks

Labels (2)
1 Solution

Accepted Solutions
yogiachilleos
Contributor III
Contributor III

Howdy Ankithreddy!

This is a fun problem! You can do =if(min(Year) + (getselectedcount(Year)-1) <> max(Year), 'Error', '')

The math works like this 2014, 2015, 2016, 2017.

Min(Year) = 2014

Max(Year) = 2017

GetSelectedCount(Year) = 4

2014 + (4 - 1) = 2017. No error will be shown.

Hope this helps!

Kind regards,

Yogi Achilleos

View solution in original post

4 Replies
yogiachilleos
Contributor III
Contributor III

Howdy Ankithreddy!

This is a fun problem! You can do =if(min(Year) + (getselectedcount(Year)-1) <> max(Year), 'Error', '')

The math works like this 2014, 2015, 2016, 2017.

Min(Year) = 2014

Max(Year) = 2017

GetSelectedCount(Year) = 4

2014 + (4 - 1) = 2017. No error will be shown.

Hope this helps!

Kind regards,

Yogi Achilleos

QlikEnth
Contributor II
Contributor II
Author

Hello Yogiachilleos,
Yes this a fun thing, thank for you response.
it did actually worked for 'Years' and working as expected almost, i just need to do some changes but logic will for sure..i should work on the same for 'Quarters' (Q1,Q2,Q3,Q4) , but should work on to come up with different approach as these are not Straight Numbers
yogiachilleos
Contributor III
Contributor III

Quarters are even more fun! Lol but you need to do some script work.

 

Temp:

Load Distinct

Year,

Quarter

Resident Calendar

Order by Year, Quarter Asc; 

 

Left Join (Calendar)

Load *,

rowno() as QuarterID

Resident Temp;

 

Drop Table Temp;

 

Then you can use a similar formula as the one above for year but on QuarterID! (You'll have to use getPossibleCount() instead of getSelectedCount() )

 

Hope that helps.

 

Kind regards,

Yogi Achilleos

Vegar
MVP
MVP

I'm thinking of a more general validation valid for all period dimensions something like this.
=If(Count({<DateField=E(DateField)*{"<$(=MAX(DateField))>$(=MIN(DateField)"}>}DateField) >0, 'Check your selections')

I haven't checked if it will work, but they idea is to count all excluded dates that lies within the interval between the min available date and the max available date. This will work on both selections in years, quarters, months, weeks and dates.

Please let me know if you try and if it behaves as intended.

-Vegar