2 Replies Latest reply: Apr 21, 2013 1:22 PM by norrsken7 RSS

    Custom date periods in comparison

      Hello, Community!

       

      The task is the following.

       

      We have calendar in our model based on this example script:

       

      LET varMinDate = Num(Peek('DG_TURDATE', 0, 'Calendar_Tur'));
      LET varMaxDate = Num(Peek('DG_TURDATE', -1, 'Calendar_Tur'));
      LET varToday = num(today());
      
      
      
      
      //*************** Temporary Calendar ***************
      DateField:
      LOAD 
        $(varMinDate) + rowno() - 1 AS Num, 
        date($(varMinDate) + rowno() - 1) AS TempDate 
      AUTOGENERATE 
        $(varMaxDate) - $(varMinDate) + 1; 
      
      //*************** Master Calendar ***************
      Calendar_Tur_Period:
      LOAD
      
      
          TempDate AS DG_TURDATE,
        Year(TempDate) AS YearTourPeriod,
          Month(TempDate) AS MonthTourPeriod,
          Day(TempDate) AS DayTourPeriod,
          Week(TempDate) AS WeekTourPeriod,
          WeekDay(TempDate) AS WeekDayTourPeriod,
          MonthName (TempDate) as MonthYearTourPeriod,
          'Quarter ' & ceil(Month(TempDate)/3) AS QuarterTourPeriod
      
      
      
      
      
      
      RESIDENT DateField 
      ORDER BY TempDate ASC;
      
      
      DROP TABLE DateField;
      
      

       

       

      Though the variables I add the functionality that the user is able to first select the Start Date and then EndDate and select this period pressing the Button with action.

       

      So QlikView selects for him only those values that are connected with this selected period, f.e. 10 January 2013 to 17 January 2013 and he is analysing the data.

       

      The issue is the following, how can we create this user path:

       

      1) User selects Start Date (f.e. 12 January), selects End Date (19 January), and then selects several years (f.e. 2013, 2012, 2011, 2010), and QlikView selects to him only the values in period of 12 January to 19 January in each year 2013, 2012, 2011....

       

      2) User for example selects 25 December 2012 as Start Date and then selects 10 January 2013 as End Date, there in addition he selects years 2013, 2012, 2011, 2010. And QlikView should select for him only those values that are connected to appropriate period:

       

                25 December 2012 to 10 January 2013

                25 December 2011 to 10 January 2012

                25 December 2010 to 10 January 2011

       

      And so on. So  the system should count the values only connected to this data set of date  periods.

       

      What the main ways I should look through to solve this issue as user estimates the experience as I described above?

       

      Thank you very much!

        • Re: Custom date periods in comparison
          Yusuf Ali

          Hi,

                What i feel why do you allow the  Option as Year , Month try to have ListBox for Days and then

          Month Year Period like 'MM-YYYY' or 'MMM-YY' so that the user can select the Period (i.e Dec-2012) and the days .

          If i am not to the point please correct me

           

          ///Yusuf

            • Re: Custom date periods in comparison

              Hi.

               

              No, this will get no result, i.e. he could select the days in list box, but it only returnes the values for that months.

               

              So, when he selects December days of 2012 and January days of 2013, without Year connection, QlikView selects all values in Decembers and Januaries, i.e. it will sum the values for january 2010 and dec 2010, then january 2011 and dec 2011 and so on.

               

              As what we look, is that when he selects december 25 to january 10, QlikView recognizes it as a discrete period, and then user should be able to select the years in which he will look for activity only in those discrete periods.

               

              Question is, how can I teach QlikView ti recognize it as period?