Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggregate with time limitations

How would I go about summing values within a specified time?  i.e. the below:

DateTimeValue
9/15/20134:00:009.16
9/15/20134:05:008.23
9/15/20134:10:008.84
9/15/20134:15:006.62
9/15/20134:20:008.34
9/15/20134:25:005.82
9/15/20134:30:007.40
9/15/20134:35:0010.75
9/15/20134:40:005.07
9/15/20134:45:007.89
9/15/20134:50:008.67
9/15/20134:55:0012.96

i would like to sume the values and divide by 12 to get my Average.

I need to this for every hour of every day -

Any help is greatly appreciated!

Thank you.

2 Replies
pgrenier
Partner - Creator III
Partner - Creator III

Hello Dawn,

Would this be of any help?

TimeData.jpg

In the Straight Table on the left, I've set the dimensions to Date and a Calculated Dimension on Time, such as =Hour(Time).

Then, in the expressions, I've added the sum of values, and also the average value.

Please find the example qvw file attached to this post.

Regards,

Philippe

swuehl
MVP
MVP

If you  want to do this in the script, you can do it similar to what Philippe suggested:

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff]';

TimeData:

LOAD *, timestamp#(timestamp(Date+Time,'MM/DD/YYYY hh'),'MM/DD/YYYY hh') as Timestamp

Inline [

Date, Time, Value

9/15/2013, 4:00:00, 9.16

9/15/2013, 4:05:00, 8.23

9/15/2013, 4:10:00, 8.84

9/15/2013, 4:15:00, 6.62

9/15/2013, 4:20:00, 8.34

9/15/2013, 4:25:00, 5.82

9/15/2013, 4:30:00, 7.40

9/15/2013, 4:35:00, 10.75

9/15/2013, 4:40:00, 5.07

9/15/2013, 4:45:00, 7.89

9/15/2013, 4:50:00, 8.67

9/15/2013, 4:55:00, 12.96

9/15/2013, 5:00:00, 9.16

9/15/2013, 5:05:00, 8.93

9/15/2013, 5:10:00, 8.84

9/15/2013, 5:15:00, 6.62

9/15/2013, 5:20:00, 9.94

9/15/2013, 5:25:00, 5.82

9/15/2013, 5:30:00, 9.40

9/15/2013, 5:35:00, 10.95

9/15/2013, 5:40:00, 9.07

9/15/2013, 5:45:00, 7.89

9/15/2013, 5:50:00, 9.67

9/15/2013, 5:55:00, 12.96

9/15/2013, 6:00:00, 9.16

9/15/2013, 6:05:00, 3.93

9/15/2013, 6:10:00, 8.34

9/15/2013, 6:15:00, 6.62

9/15/2013, 6:20:00, 9.34

9/15/2013, 6:25:00, 3.82

9/15/2013, 6:30:00, 9.40

9/15/2013, 6:35:00, 3.95

9/15/2013, 6:40:00, 9.07

9/15/2013, 6:45:00, 7.39

9/15/2013, 6:50:00, 3.67

9/15/2013, 6:55:00, 13.96 ];

LOAD Timestamp,

          avg(Value) as AvgValue

Resident TimeData group by Timestamp;