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,

[sunny_talwar]

You are trying to do this in the script or front end?

[#3]

Hi

I was trying this is Expression. I now plan to do this in script

[swuehl]

Maybe like this:

Set TimestampFormat = 'M/DD/YYYY h:mm';

LOAD Key,

  Date,

  interval(sum([Complete Time]-Start)) as Interval

GROUP BY Key, Date;

LOAD N,

    Key,

    Date,

    Start,

    [Complete Time]

FROM

[https://community.qlik.com/thread/171364]

(html, codepage is 1252, embedded labels, table is @1);

[maxgro]

if you want this, theexpression is

sum((Date#(Complete, 'MM/DD/YYYY hh:mm') - Date#(Start, 'MM/DD/YYYY hh:mm')))

formatted in number tab as interval

[sunny_talwar]

Try this:

Table:

LOAD *,

  Interval(Complete - Start, 'hh:mm') as Duration;

LOAD N,

     Key,

     Date(TimeStamp#(Date, 'M/DD/YYYY h:mm')) as Date,

     TimeStamp#(Start, 'M/DD/YYYY h:mm') as Start,

     TimeStamp#(Complete, 'M/DD/YYYY h:mm') as Complete

FROM

[https://community.qlik.com/thread/171364]

(html, codepage is 1252, embedded labels, table is @1);

Join(Table)

LOAD Key,

  Date,

  Interval(Sum(Duration), 'hh:mm')  as [Total Duration]

Resident Table

Group By Key, Date;

FinalTable:

LOAD *,

  Duration/[Total Duration] as Percentage

Resident Table;

DROP Table Table;

Output:

[petter · Accepted solution]

[#8]

If none of above worked for you, maybe your qlik settings are inpropper.

Use the below setting at the begining of the script.

SET TimestampFormat= 'MM/DD/YYYY hh:mm'; //it will ensure that qlik will recognize dates

and in front end use  expression:

=SUM(Timestamp#(Complete)-Timestamp#(Start))

in straight or pivot table to calculate sum of differnces.

[#9]

Thanks SunIndia

[#10]

Hi,

I applied the logic provided by petter-s , but it did not work initial. After applying the format you provided, it worked. This is what I do not like about QlikView. It is very very tricky.

By default, the date and time format was:

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

this did not work

I changed this to:

SET TimestampFormat= 'MM/DD/YYYY hh:mm';

It worked.

Thanks,

Raghu