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: 
Anonymous
Not applicable

Filter for date

Hi all,

I have an ordered date which I wish to create a filter on.

The filter should be as follows:

  • This week
  • Last week
  • This Month
  • Last Month
  • Last 3 months
  • Last 6 months
  • This year.

I cannot create a single dimension field for all this, the reason being it won't select all I need.

For ex: If I select this month, then it should select the components of "This Week" as well.

Could someone please give me a direction as to how I should go about this?

1 Solution

Accepted Solutions
jmvilaplanap
Specialist
Specialist

For example

LOAD

     *,

     if (num(dateField) >= num(weekstart(today())), 1, 0)     AS flag_thisWeek,

     if (num(dateField) >= num(monthstart(today())), 1, 0)     AS flag_thisMonth,

     if (num(dateField) >= num(addmonth(today(), -1)), 1, 0)     AS flag_lastMonth,

     ...

     ...

resident yourTableName

Probably you must to modify this code to adapt to your needs, but this is the idea.

Then you only have to filter the flags to 1 or 0 in a set analysis in the dashboard objects.

Regards

View solution in original post

20 Replies
ahaahaaha
Partner - Master
Partner - Master

Hi,

I think that's what you need  https://www.resultdata.com/qlik-custom-calendars/

Fill in the required intervals if necessary.

Regards,

Andrey

jmvilaplanap
Specialist
Specialist

You can create flag fields in the script, calculating this time intervals.

Anonymous
Not applicable
Author

Hi Andrey,

I am kind of a noob in this so didn't get it.

What is the significance of the below line?

//— PRECEDING LOAD: table with start and end date range fields

LOAD [Date Range], date(evaluate(range_start)) as range_start, date(evaluate(range_end)) as range_end;

Anonymous
Not applicable
Author

Hi Jose Miguel,

Could you provide me an example?

Anonymous
Not applicable
Author

Update,

I commented the above line and loaded. The filter doesn't seem to work.

Not sure what I am doing wrong.

Except the below line, do I need to change anything else?

IntervalMatch ([start_date])

Anonymous
Not applicable
Author

Any help on this please?

jmvilaplanap
Specialist
Specialist

For example

LOAD

     *,

     if (num(dateField) >= num(weekstart(today())), 1, 0)     AS flag_thisWeek,

     if (num(dateField) >= num(monthstart(today())), 1, 0)     AS flag_thisMonth,

     if (num(dateField) >= num(addmonth(today(), -1)), 1, 0)     AS flag_lastMonth,

     ...

     ...

resident yourTableName

Probably you must to modify this code to adapt to your needs, but this is the idea.

Then you only have to filter the flags to 1 or 0 in a set analysis in the dashboard objects.

Regards

Ivan_Bozov
Luminary
Luminary

Hi! Here are two great extension you can use: Climber Selection Bar | Sense Date Range Picker.

vizmind.eu
Anonymous
Not applicable
Author

While I can do this, this will mean that I have separate dimensions for each flag.

I cannot use this in a single filter, can I?

This is an approach even I took but I am stuck as to how I can show the last 3 weeks , last month or this week  as a filter?