topic Re: sum with conditional in QlikView

sum with conditional

Mon, 03 Sep 2012 01:12:25 GMT

Hello,

i need sum some rows

example

DATE	NAME	KIND OF ACTIVITY	STAR ACTIVITY	STATUS	EQUIP NAME	TOTAL TIME
31-8-12	HELMAN	M.P	22:00	OK	BTA 1	05:00 Hours
31-8-12	HELMAN	M.P	23:00	OK	BTA1	02:00 hours
1-9-12	WILMAR	EMERGENCY	08:00	PENDING	BTA2	01:00 hours
1-9-12	LEOANRDO	TEST	07:00	PENDING	BTA2	05:00 hour

I need sum the rows Total Time that have the same information as one row.

i have this comand line

=Count({$<Status={"OK"}>}Distinct [Kind of Activity]&[Fecha]&[Equipment Name])

but, this line only count.

i need use the same conditional, but i need sum the total time.

example,

sum

([Total Time])

but, i need use the conditional ({$<Status={"OK"}>}Distinct [Kind of Activity]&[DATE]&[Equipment Name])

thank you

nagaiank — Mon, 03 Sep 2012 03:03:18 GMT

You may try the following:

Aggr(Sum({$<Status={"OK"}>}[Total Time]),[KIND OF ACTIVITY]&[DATE]&[EQUIP NAME])

whiteline — Mon, 03 Sep 2012 07:36:21 GMT

In case your [Total Time] has numeric representation you can try the following:

=Sum(Aggr(Only({$<Status={"OK"}>} [Total Time]), [Kind of Activity]&[Fecha]&[Equipment Name]))

In case it's just string you also need to transform it (use instead [Total Time] in the above):

TimeStamp#(Subfield([Total Time], ' ', 1), 'hh:mm')