Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Correct set analysis with aggr() and add min time

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 IDOperationStatusDatetimeFirstTimesum(aggr(sum({<Status = {'OK'}, [FirstTime] = {'-1'}>} [Operation Time]), Operation))Operation Time
942380operation 1NOK28/08/2018 09:10-100
942380operation 1NOK28/08/2018 09:10000
942380operation 1NOK28/08/2018 09:16-100
942380operation 1NOK28/08/2018 09:16000
942380operation 1NOK28/08/2018 09:18-100
942380operation 1NOK28/08/2018 09:18000
942380operation 1NOK28/08/2018 09:23-100
942380operation 1NOK28/08/2018 09:23000
942380operation 1NOK28/08/2018 09:25-100
942380operation 1NOK28/08/2018 09:25000
942380operation 1NOK28/08/2018 10:55-100
942380operation 1NOK28/08/2018 10:55000
942380operation 1NOK28/08/2018 10:57-100
942380operation 1NOK28/08/2018 10:57000
942380operation 1NOK28/08/2018 11:12-100
942380operation 1NOK28/08/2018 11:12000
942380operation 1NOK28/08/2018 12:43-100
942380operation 1NOK28/08/2018 12:43000
942380operation 1NOK28/08/2018 12:44-100
942380operation 1NOK28/08/2018 12:44000
942380operation 1OK28/08/2018 12:48-10144,9084
942380operation 1OK28/08/2018 12:4800144,9084
942380operation2OK16/10/2018 07:37-100,4213431
942380operation2OK16/10/2018 07:3700.42134310,4213431
942380operation 3OK16/10/2018 07:37-100,3745272
942380operation 3OK16/10/2018 07:3700.37452720,3745272
942380operation 4OK16/10/2018 07:37-100,4369484
942380operation 4OK16/10/2018 07:3700.43694840,4369484
942380operation 5OK31/08/2018 13:55-101,7711771
942380operation 5OK31/08/2018 13:5501.77117711,7711771
942380operation 6OK31/08/2018 13:55-101,6411641
942380operation 6OK31/08/2018 13:5501.64116411,6411641
942380operation 7OK31/08/2018 13:55-101,5981598
942380operation 7OK31/08/2018 13:5501.59815981,5981598
Labels (2)
1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

Never mind, did it 

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

View solution in original post

3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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.

 

Oleg Troyansky

Masters Summit for Qlik is going virtual! Dec 16th - I'll teach Performance Tuning

Highlighted
Contributor III
Contributor III

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

my expected result should be like below: 

Product IDOperationStatusDatetimeFirstTimesum(aggr(sum({<Status = {'OK'}, [FirstTime] = {'-1'}>} [Operation Time]), Operation))Operation Time
942380operation 1OK28/08/2018 12:48-10144,9084
942380operation2OK16/10/2018 07:37-100,4213431
942380operation 3OK16/10/2018 07:37-100,3745272
942380operation 4OK16/10/2018 07:37-100,4369484
942380operation 5OK31/08/2018 13:55-101,7711771
942380operation 6OK31/08/2018 13:55-101,6411641
942380operation 7OK31/08/2018 13:55-101,5981598
     sum = 151,1517
       

As for date i meant: 

Product IDOperationStatusDatetimeFirstTimesum(aggr(sum({<Status = {'OK'}, [FirstTime] = {'-1'}>} [Operation Time]), Operation))Operation Time
942380operation 1OK28/08/2018 12:48-10144,9084
942380operation 1OK16/10/2018 07:37-100,4213431

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

28/08/2018 12:48-10144,9084

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

Highlighted
Contributor III
Contributor III

Never mind, did it 

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

View solution in original post