Date/Time Grouping

Wed, 10 Dec 2014 20:26:40 GMT

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

prieper — Wed, 10 Dec 2014 21:01:50 GMT

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

topic Date/Time Grouping in App Development

Date/Time Grouping

Re: Date/Time Grouping