Count of Distinct IDFIELD Aggregate problem..

[felix18807]

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

[swuehl]

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

[felix18807]

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]

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

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]

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]

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]

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]

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