Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
felix18807
Contributor III
Contributor III

Count of Distinct IDFIELD Aggregate problem..

I want to count the distinct IDField where the count of lines having a flag A or B is less than 5.

I tried this but this is obviously wrong... 

Count(Distinct if(Sum(if(FLAG='A' or FLAG='B',))<5,IDField))

Do I need to use the AGGR function

9 Replies
swuehl
MVP
MVP

I think you can try like this:

Count({<IDField = {"=Count({<FLAG ={'A','B'} >} FLAG)<5"}>} Distinct IDField)

MayilVahanan

HI

Try like this

Count(Distinct if(Sum(if(FLAG='A' or FLAG='B',1))<5,IDField))

or in set analysis

Count({<IDField = {"=Count({<FLAG={'A','B'} >} FLAG)<5"}>} Distinct IDField)

Refer set analysis for better performance

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
felix18807
Contributor III
Contributor III
Author

Thank you for the replies. I haven't tried this use of set analysis before and it has been very inciteful to see your answers.

I am still not getting to the value I need but I suspect that this is due to my poor explanation of the problem.

Please see the attached file which I hope better demonstrates my dilema.

swuehl
MVP
MVP

=Count({<[ID 2] = {"=Count({<FLAG ={'I',1} >} FLAG)<=5"}>}  [ID 2])

i.e. remove the distinct qualifier from the count and check smaller equal (<=5) in the advanced search.

felix18807
Contributor III
Contributor III
Author

Unfortunately this seems to behave unusually when factoring in the MonthYear dimension. My inability to explain what I need to achieve is at fault here. I'm very sorry.

swuehl
MVP
MVP

Try this:

=Count({<[ID 2] = {"=Count({<FLAG ={'I',1} >} FLAG)<=5"}, FLAG = {'I',1}>}  [ID 2])

Btw, I was not able to remove the selection in YearMonth, have you changed any settings to achieve this?

felix18807
Contributor III
Contributor III
Author

Sorry I locked it as it was the month I had used throughout the example.

Your solution provides the expected answer (2140) but only when Oct 2011 is selected which is very confusing. When I unlock the date field it changes to 247 in Oct 2011 which doesn't make sense to me.

Thanks again for your continued support. I am learning more about set analysis thanks to you.

swuehl
MVP
MVP

Ah sure, locking. It seems I never use this option 😉

I think it does make sense since selecting in YearMonth does affect the advanced search, the count of FLAG and comparing to <=5. What is your requirement here?

I don't have much time right now, maybe I can look into this tomorrow.

felix18807
Contributor III
Contributor III
Author

Any time you can give me is massively appreciated as I am lost on this requirement! I am very grateful for the help you've given.

It's hard for me trying to relay exactly what I am looking for but here goes. I need a count of lines per MonthYear that have the I or 1 FLAG but ONLY where the count of lines (FLAG =I or 1) for the MonthYear/ ID <=5.

I have retreated to Excel in order to get some validation values and I believe that for Oct 2011 the figure should be 2140.

It is a very confusing requirement for me but this is what my manager wants to achieve so I'm stuck