3 Replies Latest reply: Feb 18, 2013 9:14 AM by Vivien TEXIER RSS

    Master calendar with hour

    Michael Ionkin

      Hello

       

      i'm using a master calendar on the basis of days. I want to have a finer granularity and be able to present aggr values on basis 'hour'.

      A similar issue was already discussed here : http://community.qlik.com/message/106585#106585

       

      What I've already prepared is a timestamp in the correct format:

       

      Timestamp(alt(num#(Schichtdatum,'#','.',','),Timestamp#(Schichtdatum, 'DD.MM.YYYY hh'))) as Schichtdatum_Stunde_alt

       

      Please give me a hint how to go on!

       

      Here is the code im using to connect days:

       

      MaxMinDate:

      load
      num(min(Schichtdatum)) as MinDate,
      num(max(Schichtdatum)) as MaxDate

       
      resident Auftragsdaten_BDE;


      let varMinDate = peek('MinDate');
      let varMaxDate = peek('MaxDate');

      Temp_Calendar:

      Load
      $(varMinDate) + RowNo() - 1 AS DateNumber, 
      Date($(varMinDate) + RowNo() - 1) AS TempDate 
      AUTOGENERATE 1 
      WHILE $(varMinDate)+IterNo()-1 <= $(varMaxDate);


      Calendar:
      LOAD
      date(TempDate, '$(DateFormat)') AS Schichtdatum,
      date(TempDate, '$(DateFormat)') AS CalDate,
      Weekday(TempDate) AS WeekDay,
      Week(TempDate) AS Week,
      Day(TempDate) AS Day,
      Month(TempDate) AS Month,
      'Q' & ceil(month(TempDate) / 3) AS Quarter,
      Year(TempDate) AS Year,
      Week(TempDate)&'-'&Year(TempDate) AS CWeekYear,
      Date(monthstart(TempDate), 'MMM-YY') AS MonthYear,
      'Q' & ceil(month(TempDate) / 3)&'-'&Year(TempDate) AS QuarterYear


      RESIDENT Temp_Calendar ORDER BY TempDate ASC;

      DROP TABLE Temp_Calendar;