Count if the result of count is less than a number

[sstefancies]

I have used two previous posts to get as far as I have, but have resulting questions:

https://community.qlik.com/t5/New-to-QlikView/Count-if-the-result-of-count-is-less-than-a-number/m-p...

https://community.qlik.com/t5/New-to-Qlik-Sense/A-count-of-a-count-function/m-p/1571219#M133206

Example data:

wo-no, Activity

WO1, A1

WO1, A2

WO2, A1

WO2, T1

WO3, T1

WO3, T1

WO4, A1

I need to count the number of wo-nos that have fewer than 2 T1 Activities in this example.  So my resulting number should be 3 because WO3 is the only one that has 2 T1 activities.  

The first formula I used almost worked, but it wouldn't count zeros:

Sum (

if( Aggr( Count( {<[Activity]={'T1'}>} [Activity]) , [wo-no] ) < 2 , 1, 0 )

)

The second formula didn't work once I added the set expression:

sum(aggr(if(count( [Activity])<2,count([Activity])),[wo-no]))  

That would return 1 because WO4 is the only one with fewer than 2 activities total, but I only care about T1.  So I added a set expression:

sum(aggr(if(count( {<[Activity]={'T1'}>} [Activity])<2,count([Activity])),[wo-no])) 

And I'm not sure what that did since it returned 598656 when my total number to begin with was only 133.  

[sunny_talwar]

May be this

Count(DISTINCT {<[wo-no] = {"=Count({<Activity = {'T1'}>}Activity) < 2"}>} [wo-no])

[sstefancies]

That didn't work.  I'm not sure what it's counting.  It didn't return anything meaningful. 

[sunny_talwar · Accepted solution]

How about this?

Count(DISTINCT {<[wo-no] = {"=Count({<Activity = {'T1'}>}Activity) + Sum(0) < 2"}>} [wo-no])

[sunny_talwar · Accepted solution]

Based on the sample data... I get 3 using the sample

[sstefancies]

I believe this is working.  Thank you so much!