I'm trying to create measures (within Sheets - I don't have access to Load editor or the data model) to count, average, find max and min number of requests that a manager's team has (ultimately to go in a box plot). The basic unit to count is a [Request ID], complicated by multiple rows for each [Request ID] across the fields I'm interested in.
Example: (There are other fields in the data set that result in what appear to be duplicate records/rows).
Record ID
[Request ID]
[Worker ID]
[Manager ID]
1
RI001
WI001
MI001
2
RI001
WI001
MI001
3
RI001
WI002
MI002
4
RI002
WI001
MI001
5
RI002
WI003
MI001
6
RI003
WI003
MI001
7
RI004
WI004
MI002
I only want to count a [Request ID] once in the calculation and I'm fine with taking the first or last [Worker ID] (alphabetically), and excluding the others. eg my results Tables would look like this:
[Manager ID]
Count
Record ID included in Calculation
MI001
3
1 or 2, 4, 6
MI002
1
7
or
[Worker ID]
Count
Record ID included in Calculation
WI001
2
1 or 2, 4
WI002
0
WI003
1
6
WI004
1
7
I thought there might be a way to use the Aggr function to do this. In SQL I'd probably partition by [Request ID], sorting by [Worker ID], then select the first record in the partition, and count group by [Worker ID] or [Manager ID] (there is probably a better way). I'm not sure how to do this in Qlik Sense.
