Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
try to use this:
Avg(Aggr(Max(duration),ID))
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 ?
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
That worked a treat, thanks so much!
I used the 2nd version as I have multiple rows with same ID and color.
cheers🙂