Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How would I go about summing values within a specified time? i.e. the below:
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 |
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.
Hello Dawn,
Would this be of any help?
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
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;