How to Sum within a group

[#1]

Hi,

I am having hard time computing difference in time for each "Key" within each day. Can someone please suggest how to do this in QlikView? I can do this Excel and Matlab. I am not an expert, but fairly familiar with Expressions and programming in Qlik. I have the following data and we want to compute:

1. Duration = the difference between "Complete Time" minus "Start" time for each "Key within the same "Date"

2. Then sum those "Duration" for each "Key" only within each day

3. Find percentage of total duration each key took across all keys within the same day. Duration of each key divided by (Minimum(Start) time across any key - Max (Complete) time across any key for the same day.

So from below sample, we want answers to be:

On date 6-26-2013, the total duration for Key 5 will be "25 minutes" + "1 minute" = "26 minutes".

Column

- N is a unique number

- Key is identifier

- Date

- Start is starting date and time

- Complete is complete date and time

N	Key	Date	Start	Complete
11	1	6/26/2013 0:00	6/26/2013 19:06	6/26/2013 19:06
12	1	6/26/2013 0:00	6/26/2013 19:07	6/26/2013 19:33
13	2	6/26/2013 0:00	6/26/2013 19:08	6/26/2013 19:34
14	3	6/26/2013 0:00	6/26/2013 19:05	6/26/2013 19:31
15	4	6/26/2013 0:00	6/26/2013 19:05	6/26/2013 19:30
16	5	6/26/2013 0:00	6/26/2013 19:11	6/26/2013 19:36
17	5	6/26/2013 0:00	6/26/2013 19:09	6/26/2013 19:10
18	1	6/27/2013 0:00	6/27/2013 8:55	6/27/2013 8:55
19	1	6/27/2013 0:00	6/27/2013 9:18	6/27/2013 9:43
20	2	6/27/2013 0:00	6/27/2013 8:28	6/27/2013 8:28
21	2	6/27/2013 0:00	6/27/2013 8:49	6/27/2013 9:14
22	7	6/27/2013 0:00	6/27/2013 9:14	6/27/2013 9:39
23	1	6/28/2013 0:00	6/28/2013 9:05	6/28/2013 9:30
24	1	6/28/2013 0:00	6/28/2013 11:14	6/28/2013 11:39
25	2	6/28/2013 0:00	6/28/2013 8:54	6/28/2013 9:19
26	3	6/28/2013 0:00	6/28/2013 9:34	6/28/2013 9:59
27	3	6/28/2013 0:00	6/28/2013 11:27	6/28/2013 11:52
28	4	6/28/2013 0:00	6/28/2013 10:11	6/28/2013 10:36
29	7	6/28/2013 0:00	6/28/2013 11:25	6/28/2013 11:50

Thanks,

[#2]

Hi Petter,

Thank you for the answer. It finally worked. It did not work initially. After the suggestion by "Nannyogg" I changed the SET TimestampFormat, and then it worked. QlikView is very very sensitive to Date and Time format. You struggle a lot to fix minor formatting changes before you see the results. As far as programming is concerned, Excel and Matlab are extremely easy to program compared to QlikView. But QlikView has tons of nice features

Thanks again for your quick response.

Raghu