Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have used two previous posts to get as far as I have, but have resulting questions:
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.
How about this?
Count(DISTINCT {<[wo-no] = {"=Count({<Activity = {'T1'}>}Activity) + Sum(0) < 2"}>} [wo-no])
May be this
Count(DISTINCT {<[wo-no] = {"=Count({<Activity = {'T1'}>}Activity) < 2"}>} [wo-no])
That didn't work. I'm not sure what it's counting. It didn't return anything meaningful.
How about this?
Count(DISTINCT {<[wo-no] = {"=Count({<Activity = {'T1'}>}Activity) + Sum(0) < 2"}>} [wo-no])
Based on the sample data... I get 3 using the sample
I believe this is working. Thank you so much!