Group By Problem

[#1]

Hello,

I am new to in QV world and trying to put together a small analytics dashboard. I am trying to count records (activities) created by different users and to group them by 5 minutes period starting 2009-01-01 00:00:00, for each user and each activity. In the code below I am using just one activity for testing. The records get counted and grouped by user but I cannot get them grouped by 5 minute slots.

Please advise. (qvd enclosed)

TempTable1:

LOAD

    [Note Created By]as CreatedBy,

    'Note Created' as Activity,

    Interval(([Note Created On] - '2009-01-01 00:00:00')/300,'S') as CreatedOn_SSDiff,

    [Note Created On] as StartingDate

FROM

$(vProjectQvdDir)Notes.qvd (qvd);

TempTable2:

LOAD

     CreatedBy,

     Activity,

     Min(Date(StartingDate,'YYYY-MM-DD hh:mm:ss')) as SD,

     CreatedOn_SSDiff,

     Count(Activity)

Resident TempTable1

Where Year(StartingDate)=1995

Group by

CreatedBy,

Activity,

CreatedOn_SSDiff;

Drop Table TempTable1;

[rwunderlich · Accepted solution]

One problem may be that you are using [Note Created On] which doesn't include the time. I think you should use [Note Created On Full]. To assign to 5 minute buckets relative to 2009-01-01 how about:

Interval(

  interval#(

    floor((([Note Created On Full] - MakeDate(2009)) * 86400), 300)

    ,'s'

  )

,'d hh:mm') as CreatedOn_SSDiff

-Rob

[#3]

Thank you Sir!

I was a bit confused as it gave me correct time slots (in seconds) when I was to pull the information into a table or chart.