topic Re: Aggregate with time limitations in QlikView

Aggregate with time limitations

Wed, 09 Oct 2013 17:49:52 GMT

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.

Re: Aggregate with time limitations

pgrenier — Wed, 09 Oct 2013 18:16:14 GMT

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

Re: Aggregate with time limitations

swuehl — Wed, 09 Oct 2013 18:31:43 GMT

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;