Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Distinct set analysis for average duration

I need to create set analysis based around the average duration(h:hh:ss) but based on distinct IDs.

So a count of distinct ID's is count(distinct ID), average duration is avg(duration) how how do I combine into set analysis as I need another field called color = 'Red'.  My dimension is called MthYr and is in the form of Sep-19 for example.  An example of my data table is below

MthYr          ID     duration

Sep-19       29      0:33:23

Sep-19       29      0:33:23         (there are other field that differ , hence the duplicate ID rows)

Sep-19       12      0:12:12

Sep-19       12      0:12:12

Sep-19       12      0:12:12

So for Sep-19, I want to output an average duration of 0:22:48 (Avg of 0:33:23 and 0:12:12), I don't want to output 

0:20:40 (which is an average of all 5 durations)

Thank you

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

If you have only one row for each id and color, you could use Avg({<color = {'red'}>} duration),

If you have more rows with the same color and id: Avg(Aggr(Max({<color = {'red'}>}duration),ID))

 

Let me know if it works

View solution in original post

4 Replies
Highlighted
Partner
Partner

Hi,

 

try to use this: 

Avg(Aggr(Max(duration),ID))

Highlighted
Contributor III
Contributor III

Many thanks for the quick reply!  I will try that out.

Assuming that works, but how do include criteria such as color='Red', as per standard set analysis ?

Highlighted
Partner
Partner

If you have only one row for each id and color, you could use Avg({<color = {'red'}>} duration),

If you have more rows with the same color and id: Avg(Aggr(Max({<color = {'red'}>}duration),ID))

 

Let me know if it works

View solution in original post

Highlighted
Contributor III
Contributor III

That worked a treat, thanks so much!

I used the 2nd version as I have multiple rows with same ID and color.

cheers🙂