Correct set analysis with aggr() and add min time

[Jogi]

Hi guys, please help me 

Below my table

my formula is 

sum(aggr(sum({<Status = {'OK'}, [FirstTime] = {'-1'}>} [Operation Time]), Operation))

my result is 6.2433197

What i need to do: 

1) add from operation 1 Operation Time =144,9084 -, cause it should in result, honestly i don't know why it doesn't:( 

2) add condition that always select first (oldest)  time for calculating -> something like Datetime = {'$(=min(Datetime)) but i don't know how to make aggr because it select first date for Product ID, but i need for each Operation  

Thanks 

Product ID	Operation	Status	Datetime	FirstTime	sum(aggr(sum({<Status = {'OK'}, [FirstTime] = {'-1'}>} [Operation Time]), Operation))	Operation Time
942380	operation 1	NOK	28/08/2018 09:10	-1	0	0
942380	operation 1	NOK	28/08/2018 09:10	0	0	0
942380	operation 1	NOK	28/08/2018 09:16	-1	0	0
942380	operation 1	NOK	28/08/2018 09:16	0	0	0
942380	operation 1	NOK	28/08/2018 09:18	-1	0	0
942380	operation 1	NOK	28/08/2018 09:18	0	0	0
942380	operation 1	NOK	28/08/2018 09:23	-1	0	0
942380	operation 1	NOK	28/08/2018 09:23	0	0	0
942380	operation 1	NOK	28/08/2018 09:25	-1	0	0
942380	operation 1	NOK	28/08/2018 09:25	0	0	0
942380	operation 1	NOK	28/08/2018 10:55	-1	0	0
942380	operation 1	NOK	28/08/2018 10:55	0	0	0
942380	operation 1	NOK	28/08/2018 10:57	-1	0	0
942380	operation 1	NOK	28/08/2018 10:57	0	0	0
942380	operation 1	NOK	28/08/2018 11:12	-1	0	0
942380	operation 1	NOK	28/08/2018 11:12	0	0	0
942380	operation 1	NOK	28/08/2018 12:43	-1	0	0
942380	operation 1	NOK	28/08/2018 12:43	0	0	0
942380	operation 1	NOK	28/08/2018 12:44	-1	0	0
942380	operation 1	NOK	28/08/2018 12:44	0	0	0
942380	operation 1	OK	28/08/2018 12:48	-1	0	144,9084
942380	operation 1	OK	28/08/2018 12:48	0	0	144,9084
942380	operation2	OK	16/10/2018 07:37	-1	0	0,4213431
942380	operation2	OK	16/10/2018 07:37	0	0.4213431	0,4213431
942380	operation 3	OK	16/10/2018 07:37	-1	0	0,3745272
942380	operation 3	OK	16/10/2018 07:37	0	0.3745272	0,3745272
942380	operation 4	OK	16/10/2018 07:37	-1	0	0,4369484
942380	operation 4	OK	16/10/2018 07:37	0	0.4369484	0,4369484
942380	operation 5	OK	31/08/2018 13:55	-1	0	1,7711771
942380	operation 5	OK	31/08/2018 13:55	0	1.7711771	1,7711771
942380	operation 6	OK	31/08/2018 13:55	-1	0	1,6411641
942380	operation 6	OK	31/08/2018 13:55	0	1.6411641	1,6411641
942380	operation 7	OK	31/08/2018 13:55	-1	0	1,5981598
942380	operation 7	OK	31/08/2018 13:55	0	1.5981598	1,5981598

[Oleg_Troyansky]

Hi there!

This is exactly the topic that I will be teaching in a couple of hours at the Masters Summit for Qlik in Washington, DC 🙂 I wish you could be here, you'd know everything by the end of the day. Let me give you a few directions to explore:

- You don't see the expected results because your AGGR dimensions are less granular than your Chart Dimensions. This is what I call in my book "The third law of AGGR", and what Henric Cronstrom calls the "grain mismatch" problem. Add all the dimensions of the chart into your formula, and you should be able to see the expected results in every line.

- I'm not sure I understand your requirement about "the oldest" record should be considered. Maybe you could define it with a conditional flag, or formulate a Set Analysis filter that only selects the oldest record. Another good function to explore here is FirstSortedValue - maybe that is what will produce the desired result.

- I don't quite understand the need in the AGGR function. Both of your aggregation functions (internal and external) are Sums. What kind of a special logic are you trying to achieve with the use of AGGR?

Cheers,

Oleg Troyansky 

Check out my book QlikView Your Business - the expert guide to QlikView and Qlik Sense.

[Jogi]

Hi i should be in Washington DC ;), but please help me at least for now: 

my expected result should be like below: 

Product ID	Operation	Status	Datetime	FirstTime	sum(aggr(sum({<Status = {'OK'}, [FirstTime] = {'-1'}>} [Operation Time]), Operation))	Operation Time
942380	operation 1	OK	28/08/2018 12:48	-1	0	144,9084
942380	operation2	OK	16/10/2018 07:37	-1	0	0,4213431
942380	operation 3	OK	16/10/2018 07:37	-1	0	0,3745272
942380	operation 4	OK	16/10/2018 07:37	-1	0	0,4369484
942380	operation 5	OK	31/08/2018 13:55	-1	0	1,7711771
942380	operation 6	OK	31/08/2018 13:55	-1	0	1,6411641
942380	operation 7	OK	31/08/2018 13:55	-1	0	1,5981598
					sum =	151,1517

As for date i meant: 

Product ID	Operation	Status	Datetime	FirstTime	sum(aggr(sum({<Status = {'OK'}, [FirstTime] = {'-1'}>} [Operation Time]), Operation))	Operation Time
942380	operation 1	OK	28/08/2018 12:48	-1	0	144,9084
942380	operation 1	OK	16/10/2018 07:37	-1	0	0,4213431

so if we have one Product ID  and one Operation i need to select only first Operation time 

28/08/2018 12:48

-1

0

144,9084

because it was early, let me know if you have any questions please 

[Jogi · Accepted solution]

Never mind, did it 

=sum({$<Status = {'OK'}, [TimeExecuted] = {'-1'}>} Aggr
(If
(
Status = FirstSortedValue(TOTAL <Operation> Status, Datetime), [Operation Time]
), Operation, Status
)
)