Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
sstefancies
New Contributor III

Count if the result of count is less than a number

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.  

Labels (3)
2 Solutions

Accepted Solutions

Re: Count if the result of count is less than a number

How about this?

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

Re: Count if the result of count is less than a number

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

image.png

5 Replies

Re: Count if the result of count is less than a number

May be this

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

Re: Count if the result of count is less than a number

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

Re: Count if the result of count is less than a number

How about this?

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

Re: Count if the result of count is less than a number

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

image.png

Highlighted
sstefancies
New Contributor III

Re: Count if the result of count is less than a number

I believe this is working.  Thank you so much!