3 Replies Latest reply: Feb 28, 2018 2:37 PM by Bill Markham RSS

    How to set date range in filter pane?

    Toby Booth

      Hello everyone,


      My first post. I'm very new to Qliksense and have found a script (see below) that suits my purposes very well. However I am having difficulty understanding how to use my own data in it, instead of the auto-generated data the script provides as an example. Can anyone help?


      For context, I got the script from this thread, https://community.qlikview.com/message/256243#256243





      /* This section just creates some test data */


      LET vEndDate = num(today(1)); // End at today's date

      LET vStartDate = num(addmonths(today(1),-14)) -1; // Start at 14 months earlier than today


      LOAD date($(vStartDate) + IterNo()) as Date


      WHILE $(vStartDate) + IterNo() <= $(vEndDate)


      /* End of test data */


      /* This script demonstrates assigning Dates to named groups, such as "Yesterday" or "Last Week".

      Date table was created on the previous tab.



      // Assign current date to a variable. Makes INLINE below a little neater and ensures we don't cross midnight boundry.

      LET vToday=today(1);



      // Use the "evaluate()" function to execute the input function text and convert it to actual date values.

      LOAD Range, date(evaluate(RangeStart)) as RangeStart, date(evaluate(RangeEnd)) as RangeEnd


      /* The INLINE data defines the Range Name, Starting date of the Range, and Ending date of the Range.

      The Start/End values are defined using standard QV date functions.

      Semicolon is used for a field delimeter because some of the functions contain commas.


      LOAD * INLINE [

      Range; RangeStart; RangeEnd

      Today; vToday; DayEnd(vToday)

      Yesterday; vToday-1; DayEnd(vToday-1)

      This Week; WeekStart(vToday); WeekEnd(vToday)

      Last Week; WeekStart(vToday,-1); WeekEnd(vToday, -1)

      Last 2 Weeks; WeekStart(today(1),-1); WeekEnd(today(1))

      This Month; MonthStart(vToday); MonthEnd(vToday)

      Last Month; MonthStart(vToday,-1); MonthEnd(vToday,-1)

      Last Two Months; MonthStart(vToday,-1); MonthEnd(vToday)

      This Year; YearStart(vToday); YearEnd(vToday)

      Last Year; YearStart(vToday,-1); YearEnd(vToday,-1)

      ] (delimiter is ';')




      Use IntervalMatch to link the Date field into multiple Range fields.

      Doing a JOIN avoids creating a messy synthetic key.


      JOIN (Ranges) IntervalMatch (Date) LOAD RangeStart, RangeEnd RESIDENT Ranges;

      // Because we did a JOIN, we may drop the the Start/End fields.

      DROP FIELDS RangeStart, RangeEnd;