Set Analysis Count of Counts

[Jenny]

Hi,

I have a table of client purchases. Each client gets a classification according to the purchases.

ClientID StoreID Classification_group
10 1 A
20 2 A
30 3 B
40 4 C
50 3 C
60 2 C
70 1 B
80 1 C
90 3 A
100 4 A
110 1 C
120 1 C
130 3 B
140 2 C

I want to count the number of StoreIDs that have a count of a specific group C that is 2 and above.

Basically if there is a storeID that the count of group C is 2 or more then Count these stores.

My Set Analysis only counts group C per Store but I cant do the counting of the stores afterwards:

count({$<StoreID = {'=count(Classification_group)>=2'},Classification_group={'C'}>}StoreID)

What am I missing? Thank you for your help 🙂

[Kushal_Chawda]

@Jenny  could you elaborate and tell me what should be the count in this case?

[BrunPierre]

Hi, from what I understand perhaps as below;

=Count(Distinct Aggr(If(Count({<Classification_group={'C'}>} ClientID) >= 2, StoreID), StoreID))

[marksouzacosta · Accepted solution]

Hi @Jenny,

You are missing a few things in your expression. Try this - note the double quotes instead of single:

Count({<StoreID = {"=Count({<Classification_group = {'C'}>} Classification_group) >= 2"}>} DISTINCT StoreID)

Or maybe this:

Count({<StoreID = {"=Count({<Classification_group = {'C'}>} Classification_group) >= 2"}, Classification_group = {'C'} >} StoreID)

Regards,

Mark Costa

[marcus_sommer]

You may try:

count({$<Classification_group={'C'}>} StoreID) * -(count(Classification_group)>=2)

[Jenny]

Hi. Yes.

So in this case if I filter only group C. I get 2 stores that have C count larger or equal to 2:

ClientID StoreID Group
80 1 C
110 1 C
120 1 C
60 2 C
140 2 C
50 3 C
40 4 C

Store number 1 and store number 2. So the answer should be: 2 stores have C count larger or equal to 2

[Jenny]

Thank you! It works 🙂