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.
