Is there a way in qlikview to set an expression to sum over an hour's time?

[#1]

I'm trying to graph data that is taken every 5 minutes, and I'd like to display a sum of all those points per hour. So, in other words, instead of a line graph of data like this:

Time | Connections

00:00 | 48

00:05 | 22

00:10 | 36

...

00:55 | 12

I'd like the line graph to display the following data:

Time | Connections

00:00 | 1000

01:00 | 657

Thanks for any help.

[#2]

Hi there,

You can extract the hour component of the time field to create another field. Then you can use the new "hour" field as a dimension for your chart. Then QV will automatically group the sums by hour.

For example:

LOAD

TimeField,

Text(Time(TimeField,'HH')) as HourField,

:

:

[#3]

Thanks for the suggestion. I now have: "timestamp(time,'M/D/YYYY h tt')", which doesn't display correctly. I still get individual data points from 5 minute intervals, not a sum of the data over an hour.

[rwunderlich]

Your timestamp expression only formats the display value. The values are still the original 5 minute incremments.

You must extract just the hour, for example like this:

hour(time)

-Rob

[#5]

Rob,

Thanks. Using hour(time) or hour(x) displays "no data to display".

[#6]

Note: hour(x) meaning anything else I tried.

[#7]

Are there any other suggestions? I still can't get this to work.

[#8]

Did you find any solution? I'm fighting with the same problem.

[#9]

I did... I first extracted the hour field via sql:

date_trunc('hour',time_field) AS one_hour

Next, I used the the following statement to properly display the relevant dimensions:

=timestamp(floor(one_hour*24)/24,'YYYY-MM-DD hh tt')

Qlikview handled the rest.