Distinct set analysis for average duration

[guster999]

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

[StarinieriG]

Hi,

try to use this: 

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

[guster999]

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 ?

[StarinieriG · Accepted solution]

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

[guster999]

That worked a treat, thanks so much!

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

cheers🙂