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

[quickqlik2]

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?

[Ahidhar]

something like this ?

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

[Aditya_Chitale · Accepted solution]

@quickqlik2 ,

First of all, use distinct load in your script editor while loading data. This will remove any duplicate entries in loading part itself.

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,

Aditya