Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

nicklisterman
Contributor II

Average of Count by Hour

I have incident data stored like the below and I need to calculate the average number of tickets created by hour each day.  I know I need to use AGGR() but I just can't quite get it right.

TicketDateHour
Ticket13/4/20161
Ticket23/5/20161
Ticket33/5/20161
Ticket43/6/20161
Ticket53/6/20161
Ticket63/6/20161
Ticket73/6/20161
Ticket83/6/20161
Ticket93/6/20161
Ticket13/4/20162
Ticket23/5/20162
Ticket33/5/20162
Ticket43/6/20162
Ticket53/6/20162
Ticket63/6/20162
Ticket73/6/20162
Ticket83/6/20162
Ticket93/6/20162

  

In the end I want to see:

HourAverage
13
23
5 Replies
jolivares
Valued Contributor

Re: Average of Count by Hour

You can use:

Avg(Aggr(Count(Ticket),Date,Hour))

Highlighted
MVP
MVP

Re: Average of Count by Hour

avg(aggr(Count(distinct Ticket), Hour, Date))

nicklisterman
Contributor II

Re: Average of Count by Hour

So I didn't even think to just throw Date, Hour together into the Aggr() function.  You both responded almost identically but Maxgro listed Hour first.  Does order matter?

MVP
MVP

Re: Average of Count by Hour

no

Re: Average of Count by Hour

In some circumstances it might matter (unless you have QV12, where we have work around available), but for this particular scenario it won't.