20 Replies Latest reply: Jul 14, 2017 7:40 AM by Siddharth Sheshadri

Filter for date

Siddharth Sheshadri Jun 30, 2017 6:56 AM

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?

Re: Filter for date

Andrey Khoronenko Jun 30, 2017 8:49 AM (in response to Siddharth Sheshadri)

Hi,

I think that's what you need https://www.resultdata.com/qlik-custom-calendars/
Fill in the required intervals if necessary.

Regards,
Andrey
Like Show 0 Likes (0)

Actions
- Re: Filter for date
  
  Siddharth Sheshadri Jul 1, 2017 6:24 AM (in response to Andrey Khoronenko)
  
  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;
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Filter for date
    
    Siddharth Sheshadri Jul 1, 2017 6:45 AM (in response to Siddharth Sheshadri)
    
    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])
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: Filter for date
      
      Siddharth Sheshadri Jul 3, 2017 5:59 AM (in response to Siddharth Sheshadri)
      
      Any help on this please?
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Filter for date
        
        Andrey Khoronenko Jul 3, 2017 8:20 AM (in response to Siddharth Sheshadri)
        
        Hi,
        
        Look attached file
        
        !!AutoCalendar+Selection.qvf 192.0 K
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Filter for date
        
        Siddharth Sheshadri Jul 3, 2017 10:57 AM (in response to Andrey Khoronenko)
        
        Thanks, will check it out & let you know.
        
        Like Show 0 Likes (0)
        
        Actions
Re: Filter for date

Jose Miguel Vilaplana Jun 30, 2017 9:31 AM (in response to Siddharth Sheshadri)

You can create flag fields in the script, calculating this time intervals.
Like Show 0 Likes (0)

Actions
- Re: Filter for date
  
  Siddharth Sheshadri Jul 1, 2017 6:25 AM (in response to Jose Miguel Vilaplana)
  
  Hi Jose Miguel,
  
  Could you provide me an example?
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Filter for date
    
    Jose Miguel Vilaplana Jul 3, 2017 6:09 AM (in response to Siddharth Sheshadri)
    
    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
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: Filter for date
      
      Siddharth Sheshadri Jul 3, 2017 7:47 AM (in response to Jose Miguel Vilaplana)
      
      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?
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Filter for date
        
        Jose Miguel Vilaplana Jul 3, 2017 7:52 AM (in response to Siddharth Sheshadri)
        
        Is the same, you can add buttons with the trigger "Selection -> Select in Field" and use this expressions for filter. And when the user clicks on "Last Week", apply the filter flag_lastWeek equals to 1.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Filter for date
        
        Siddharth Sheshadri Jul 3, 2017 7:54 AM (in response to Jose Miguel Vilaplana)
        
        Could you elaborate this please?
        Or give me an example qvf? Sounds interesting.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Filter for date
        
        Jose Miguel Vilaplana Jul 3, 2017 8:31 AM (in response to Siddharth Sheshadri)
        
        This is a capture of Qlikview, but I think will be the same in QlikSense, you can add a trigger to an object (in this case a Text Object) and when you click it, this selection is made.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Filter for date
        
        Siddharth Sheshadri Jul 3, 2017 9:42 AM (in response to Jose Miguel Vilaplana)
        
        Ah, okay. I am working on Qlik Sense.
        So I think I will have to make use of an extension for this. This feature is not available in Qlik Sense by default.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Filter for date
        
        Jose Miguel Vilaplana Jul 3, 2017 9:55 AM (in response to Siddharth Sheshadri)
        
        Yes, this solution is not valid for QS, I'm sorry. You would use an extension (or find a workaround)
        
        good luck!
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Filter for date
        
        Siddharth Sheshadri Jul 3, 2017 10:57 AM (in response to Jose Miguel Vilaplana)
        
        Thanks.
        
        Trying with this approach and the below extension. Will let you know how I fare:
        
        Qlik Branch
        
        Thanks Jose Miguel.
        
        Regards,
        SS.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Filter for date
        
        Siddharth Sheshadri Jul 14, 2017 7:40 AM (in response to Siddharth Sheshadri)
        
        Thanks for the ideas Jose. I was able to achieve it with the above method.
        A bit of a workaround but still good enough.
        
        Thanks.
        
        Like Show 0 Likes (0)
        
        Actions
Re: Filter for date

Ivan Bozov Jul 3, 2017 7:47 AM (in response to Siddharth Sheshadri)

Hi! Here are two great extension you can use: Climber Selection Bar | Sense Date Range Picker.
Like Show 0 Likes (0)

Actions
- Re: Filter for date
  
  Siddharth Sheshadri Jul 3, 2017 7:57 AM (in response to Ivan Bozov)
  
  Thanks Ivan. However it only has a few selections.
  Doing something on our own will mean endless possibilities which is what I am aiming for.
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Filter for date
    
    Ivan Bozov Jul 3, 2017 8:11 AM (in response to Siddharth Sheshadri)
    
    You could also assign custom ranges with the Climber Selection Bar. But of course, use whatever is best for you.
    
    Like Show 1 Like (1)
    
    Actions

Go to original post