CountIf issues

[#1]

Hi,

I have a data set similar to:

CourseNumber          City

1                              New York

2                              Miami

2                              Miami

2                              San Francisco

2                              San Francisco

3                              Chicago

3                              Orlando

3                              New Jersey

4                              Orlando

5                              Boston

6                              Boston    

And I am trying to count how many courses have a count of distinct cities higher than 2. I think I have to use aggr, but I am a but lost, any suggestions?

Thanks

[sunny_talwar · Accepted solution]

May be this:

Count(DISTINCT {<CourseNumber = {"=Count(DISTINCT City) > 2"}>} CourseNumber)

or

Count(DISTINCT {<CourseNumber = {"=Count(City) > 2"}>} CourseNumber)

So based on the sample, you would see a count of 1 which would be CourseNumber 3

[qlik_jaijhn]

I think, this might help you..

=Count({<CourseNumber={'>2'}>}DISTINCT City)

[#4]

Thanks Sunny

[#5]

It seems that counting from the perspective of the course number, gives me a higher count when I run the formula against my full data set. But thanks anyways John.