Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Pomna
Creator III
Creator III

Filtering Data

Below is my set expression which gives me values for last month but I would like to create a filter that would enable me see data for other months too like last two months or last  three months. Thanks for your suggestions

 

Sum({$<[Work Date.autoCalendar.Year] = {'$(=year(today()))'}
,[Work Date.autoCalendar.Month] = {'$(=month(AddMonths(Today(),-1)))'}
,[IS.Activity Type] = {[Enhancing]}
,[Supported Location] =- {'04'}>}Hours)

1 Solution

Accepted Solutions
andoryuu
Creator III
Creator III

I'd recommend adding a variable to your app - let's call it vYearsBack. You set it to default to 1. Have a variable editing box that allows a user to enter number of years to go back. Then change your set analysis to this:
Sum({$<[Work Date.autoCalendar.Year] = {'$(=year(today()))'}
,[Work Date.autoCalendar.Month] = {'$(=month(AddMonths(Today(),$(vYearsBack)*-1)))'}
,[IS.Activity Type] = {[Enhancing]}
,[Supported Location] =- {'04'}>}Hours)

I have successfully tested this in Qlik Sense September 2019 Patch 2.

View solution in original post

4 Replies
andoryuu
Creator III
Creator III

I'd recommend adding a variable to your app - let's call it vYearsBack. You set it to default to 1. Have a variable editing box that allows a user to enter number of years to go back. Then change your set analysis to this:
Sum({$<[Work Date.autoCalendar.Year] = {'$(=year(today()))'}
,[Work Date.autoCalendar.Month] = {'$(=month(AddMonths(Today(),$(vYearsBack)*-1)))'}
,[IS.Activity Type] = {[Enhancing]}
,[Supported Location] =- {'04'}>}Hours)

I have successfully tested this in Qlik Sense September 2019 Patch 2.

View solution in original post

Pomna
Creator III
Creator III
Author

Thank you so much andoryuu. It worked like magic

 

 

 

Pomna
Creator III
Creator III
Author

One more issue if you please. If I want my variable editing box to show month names instead of numbers how do I go about it?  I would like the user to be able to select month name and year. Thank you

andoryuu
Creator III
Creator III

No problem - Just post it as a separate question and I can give details under that post (that way it'll be searchable by others).  I'll be able to detail that tomorrow (or someone may beat me to the punch!).