3 Replies Latest reply: Jun 20, 2016 1:42 AM by Brijesh Maurya RSS

    Master Calendar question

    Francois Tremblay

      Hello!

       

      I would like to use the typical Master Calendar coding created by the autogenerated coding, that looks as follow:

       

      [autoCalendar]:

         DECLARE FIELD DEFINITION Tagged ('$date')

      FIELDS

         Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

         Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter'),

         Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter'),

         Month($1) AS [Month] Tagged ('$month'),

         Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth'),

         Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),

         Date(Floor($1)) AS [Date] Tagged ('$date');

       

       

      DERIVE FIELDS FROM FIELDS [TRANDATE], [OESHDT.AUDTDATE], [ORDDATE], [SHIPDATE], [ARCUS.AUDTDATE], [DATELASTMN], [DATESTART], [DATELASTST], [DATEINVCHI], [DATEBALHI], [DATELASTAC], [DATELASTIV], [DATELASTPA], [DATELASTAD], [DATELASTRI], [AUDTDATE], [ARGRO.DATELASTMN], [ICITEM.AUDTDATE], [ICITEM.DATELASTMN], [DATEINACTV], [ICCATG.AUDTDATE], [ICCATG.DATELASTMN], [ICLOC.AUDTDATE], [ICLOC.DATELASTMN], [ARSAP.AUDTDATE], [ARSAP.DATELASTMN] USING [autoCalendar] ;

       

      __________________________________________

       

      In my database, all dates are formatted this way: YYYYMMDD for example: 20160619. But in Qlik Sense it ends up looking like this '11/01/59876'.

       

      My Set Date format is:  SET DateFormat='DD/MM/YYYY';

       

      So please, what is missing in my AutoCalendar section?

       

      Thanks for your help & suggestions!

        • Re: Master Calendar question
          Stefan Wühl

          Try

           

          SET DateFormat='YYYYMMDD';


          Or use your original default format and interpret all your input fields containing dates using Date#(FIELD,'YYYYMMDD')


          Why don’t my dates work?

          Get the Dates Right

           

          If your date field values were interpreted correctly as dates, then you can format your values using Date() function (e.g. in the master calendar code section).

          • Re: Master Calendar question
            Michael Goichmann

            Qlik Sence stores each date, time and time stamp found in data as a datetime Serial number.

             

            The date time for 01/01/2014 06:00:00 is represented as 41640.25 where:

            - The integer part, 41640, represents the date and is the number of days elapsed since Dec.30, 1899.

            - The decimal part represents the time stored as a value between 0 and 1. This nummber is the faction of elapsed time from a full day. Therefor, 06:00:00 is six hours elapsed from a full day, or 0,25. The value 0.0 corresponds to 00:00:00, 0.99999 coresponds to 23:59:59.

             

            Example:

            Value: 41025

            Format: 'M/D/YYYY'

            Text displayed: 4/26/2012

             

            When you load data into Qlik Sence, fields containing numbers with a defined data type in a database are handled according to their respective formats.

             

            You can use to define input and Output Formats of Dates:

             

            - Data(), Time() --> Formating functions define the Format for how to present the data in Qlik Sence

            - Data#(), Time#() -->   Number Interpretation function that define the data Format of the date being loaded into Qlik Sence.

             

            BR

            • Re: Master Calendar question
              Brijesh Maurya

              Hi,

               

              In Your Script u can do like this..

               

              Date(Date#(DateField,'ÝYYYMMDD'')) as DateField.

               

              So it will inform to the qlik that its a date field in YYYYMMDD Field ..

               

              Hope this Helps