Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Unique Count by Group, but only allowing one count per item

`ShirtTable:LOAD * INLINE [    ShirtID, ShirtSize, ShirtSizePriority    A, Small, 3    A, Large, 1    B, Large, 1    B, Large, 1    C, Small, 3    C, Medium, 2];`

The code above can simulate my raw data which looks like this:

 ShirtID ShirtSize ShirtSizePriority A Small 3 A Large 1 B Large 1 B Large 1 C Small 2 C Medium 1

I want to count the unique ShirtID by ShirtSize. The trick is I can only count each ShirtID one time, and have to prioritize the count according to ShirtSize. Large is first priority [1], Medium is second priority [2], Small is last priority [3].

So, I'd look at ShirtID "A" and go to the largest size "Large" and that's the count of "1" for ShirtID "A" ShirtSize "Large". Then I move to ShirtID "B" and count the largest size once, which is another count of "1" for size "Large". Remember I can only count ShirtID "B" ShirtSize "Large" once because I'm only allowed one count per ShirtID. Finally I look at ShirtID  "C" and count the largest size "Medium" once for a count of "1".

I then tally all the counts by ShirtSize and get:

 ShirtSize Count of Unique Shirt ID (only allowing the largest ShirtSize to be counted, and only counted 1 time) Small 0 Medium 1 Large 2

What's the set analysis I can use to achieve these counts for a bar graph?

Labels (3)

• ### Set Analysis

1 Solution

Accepted Solutions
Specialist

Once done with that, you can use below expression to find ShirtSize Priority wise count:

=count(
if(ShirtSizePriority = Aggr(nodistinct min(ShirtSizePriority),ShirtID),
Aggr(nodistinct min(ShirtSizePriority),ShirtID) , null() )
)

Output:

Regards,

2 Replies
Creator III

something like this ?

count(aggr(count(ShirtID),ShirtSize,ShirtID))

Specialist