1 Reply Latest reply: Dec 10, 2014 4:01 PM by Peter Rieper RSS

    Date/Time Grouping

      Greetings!!

       

      How would i take a date column named CREAT_DTTM that outputs (from SQL Server) as the below format...

      Creat_DTTM

      -----------------------------------------------------------

      2013-06-14 12:48:11.000

      2014-03-22 09:24:12.000

      2014-11-28 16:11:50.000

       

      And have it come out grouped by month with it specified as the 1st of each month, i.e.

      2013-06-14 12:48:11.000 = 6/1/2013

      2014-03-22 09:24:12.000 = 3/1/2014

      2014-11-28 16:11:50.000 = 11/1/2014

       

      The days of the month do not matter, as the users only want to see MONTHLY.  This is my script i wrote to perform this method in SQL: select CAST(MONTH(Creat_Dttm) AS VARCHAR(2)) + '/1/' + CAST(YEAR(Creat_Dttm) AS VARCHAR(4)) as MONTH 

      But i need to know how i would do that in QlikView!!

       

      Also:  As a sidenote, is there any code that could take the result of this and instead of outputting date by numbers, it could output the months by name and year?  I.e. (This question is far less important than the one above, i just thought it would be cool if it was possible).

       

      2013-06-14 12:48:11.000 = 6/1/2013 = June 2013

      2014-03-22 09:24:12.000 = 3/1/2014 = March 2014

      2014-11-28 16:11:50.000 = 11/1/2014 = November 2014

       

      Many thanks to those of you who will take the time out of your busy schedules to help me out

        • Re: Date/Time Grouping
          Peter Rieper

          You may read the field using the TIMESTAMP#-function, to teach QV that this is a timestamp, for the 1st of each month you may use the MONTHSTART-function, e.g.:

          LOAD

          *

          DATE(FLOOR(MONTHSTART(MyTimestamp)))                                   AS MonthStart;

          LOAD

          TIMESTAMP(TIMESTAMP(YourField, 'YYYY-MM-DD hh:mm:ss.000')    AS MyTimestamp

          ....


          HTH Peter