3 Replies Latest reply: Nov 22, 2011 2:50 PM by Swarup Malli RSS

    Need Help with Creating Start and End Date Variables

      So I've spent way too much time trying to search for this and am getting nowhere.  Exactly HOW do I set up start and end date variables for use with a slider/calendar object?  What I need is for the end user to be able to select a start date, then an end date, and results will return within those dates.  I can do this in SSRS just fine, but with Qlikview, not so much.

       

      Folks, I need a step-by-step which a newbie can understand.  I need this ASAP.  If I sound frustrated then you're correct - I don't have all day to figure this out and nothing I've tried works.  There's nothing about this anywhere in the tutorial that I can find, and i'm to the point where I just need results.  There needs to be a place where this is listed somehow.

       

      Can someone list the steps, or at least point me in the direction of a very good resource which will make sense to a newbie?

        • Re: Need Help with Creating Start and End Date Variables
          Swarup Malli

          Hey,

           

            Don't get frustated,you have posted ur doubts at the right place, Ppl here are very helpful. Its a very simple prob, and has a very simple solution.

           

          Which every QV developer solves by creating a master table.You find a master table in every QV application, if u check any  sample QV application u can find on the QV website u'll see it , u'll find it in the script editor.

           

           

          1st u have a create a temp table to find min and max date .Once that is found, u can auto generate the dates b/w min and max.

           

          Let me paste a sample here

           

          Temp:

          Load

              MIN(yourdatefield) as minDate,

              MAX(yourdatefield) as maxDate,

           

          RESIDENT

             yourtablewhich_contains_the_date_value;

           

          Let vMinDate = NUM(PEEK('minDate',0,'Temp'));

          Let vMaxDate = NUM(PEEK('maxDate',0,'Temp'));

          //Let vCurrentCamp = NUM(PEEK('maxCampYear',0,'Temp'));

           

          DROP TABLE Temp;

           

          //varMinDate to varMaxDate

          TempCalendar:

          LOAD

              $(vMinDate)+IterNo()-1 AS Num,

              Date($(vMinDate)+IterNo()-1) AS TempDate

          AUTOGENERATE 1 WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);

           

          //Building the master calendar with most date dimensions

           

          MasterCalendar:

          LOAD

              TempDate AS TRANSACTIONDATE,

              Year(TempDate) as Year,

          //    'Q'& CEIL(MONTH(TempDate)/3) as QUARTER,

              Month(TempDate) as Month,

              pick(num#(text(Date(TempDate,'M'))),9,10,11,12,1,2,3,4,5,6,7,8) as MonthOrder,

              DUAL(text(Month(TempDate)),pick(num#(text(Date(TempDate,'M'))),9,10,11,12,1,2,3,4,5,6,7,8)) as MonthName,

          //    week(TempDate) as Week,

              DUAL(weekstart(TempDate,0,-1) & ' - ' & weekend(TempDate,0,-1),weekstart(TempDate,0,-1)) AS Week,

              Day(TempDate) as Day,

          //    InYearToDate(TempDate,'$(vToday)',0) AS CurYearToDate,

          //    InQuarterToDate(TempDate,'$(vToday)',0) AS CurQuarterToDate,

          //    InMonthToDate(TempDate,'$(vToday)',0) AS CurMonthToDate,

          //    InYearToDate(TempDate,'$(vLastYearToday)',0) AS LastYearToDate,

          //    InQuarterToDate(TempDate,'$(vLastYearToday)',0) AS LastQuarterToDate,

          //    InMonthToDate(TempDate,'$(vLastYearToday)',0) AS LastMonthToDate,

          //    'Q'& CEIL(MONTH(TempDate)/3)&'-'&Year(TempDate) AS QuarterYear,

              date(monthstart(TempDate),'MMM-YYYY') AS MonthYear

          //    Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

          //    weekday(TempDate) as WeekDay

          RESIDENT

              TempCalendar

          ORDER BY

              TempDate Asc;

           

          DROP TABLE TempCalendar;