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

       

      Thanks.

      ---

       

      /* This section just creates some test data */

      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

      TestData:

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

      AUTOGENERATE 1

      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);

       

      Ranges:

      // 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;