topic Re: Count of Distinct IDFIELD Aggregate problem.. in QlikView

Count of Distinct IDFIELD Aggregate problem..

felix18807 — Mon, 26 Nov 2012 16:26:59 GMT

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

Re: Count of Distinct IDFIELD Aggregate problem..

swuehl — Mon, 26 Nov 2012 16:32:01 GMT

I think you can try like this:

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

Re: Count of Distinct IDFIELD Aggregate problem..

MayilVahanan — Mon, 26 Nov 2012 17:05:03 GMT

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

Re: Count of Distinct IDFIELD Aggregate problem..

felix18807 — Tue, 27 Nov 2012 10:27:12 GMT

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.

Re: Count of Distinct IDFIELD Aggregate problem..

swuehl — Tue, 27 Nov 2012 10:58:32 GMT

=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.

Re: Count of Distinct IDFIELD Aggregate problem..

felix18807 — Tue, 27 Nov 2012 13:16:34 GMT

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.

Re: Count of Distinct IDFIELD Aggregate problem..

swuehl — Tue, 27 Nov 2012 13:45:55 GMT

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?

Re: Count of Distinct IDFIELD Aggregate problem..

felix18807 — Tue, 27 Nov 2012 13:59:43 GMT

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.

Re: Count of Distinct IDFIELD Aggregate problem..

swuehl — Tue, 27 Nov 2012 14:19:54 GMT

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.

Re: Count of Distinct IDFIELD Aggregate problem..

felix18807 — Tue, 27 Nov 2012 14:43:00 GMT

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