topic How to Sum within a group in QlikView

How to Sum within a group

Mon, 06 Jul 2015 16:42:42 GMT

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,

Re: How to Sum within a sub loop

sunny_talwar — Mon, 06 Jul 2015 16:48:04 GMT

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

Re: How to Sum within a sub loop

Mon, 06 Jul 2015 16:49:59 GMT

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

Re: How to Sum within a sub loop

swuehl — Mon, 06 Jul 2015 16:52:00 GMT

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);

Re: How to Sum within a sub loop

maxgro — Mon, 06 Jul 2015 16:53:55 GMT

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

Re: How to Sum within a sub loop

sunny_talwar — Mon, 06 Jul 2015 16:58:22 GMT

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:

Re: How to Sum within a sub loop

petter — Mon, 06 Jul 2015 17:12:33 GMT

Re: How to Sum within a sub loop

Mon, 06 Jul 2015 17:26:44 GMT

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.

Re: How to Sum within a sub loop

Mon, 06 Jul 2015 18:52:03 GMT

Thanks SunIndia

Re: How to Sum within a sub loop

Mon, 06 Jul 2015 18:56:25 GMT

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

Re: How to Sum within a sub loop

Mon, 06 Jul 2015 19:01:08 GMT

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