Counting sum in variable

[#1]

Dear all

Currently I'm working on a QV app. on applications to our University.

However, I have a problem with counting the number of "small educations" (those educations that received 10 or less applications with 1. Priority) in each of my application years (2005-2013).

My expression looks like this:

=count({$<[Education by city]={"=sum({<Priority={'1. Priority'}>} Number_of_students)<11"}>} distinct [Education by city])

And it works fine whenever I select only one Year (2011 as an example underneath). But when I want 2 or more years to show, the expression counts applications for each Education regardless of which Year they are received. For example if the Education "Biology" received 9 applications in 2011 it's considered a "small education" and should therefore count as +1 in the column "Small Educations" in 2011. However if I choose 3 Years (2011, 2012, 2013) Biology received: 2011: 9 appl., 2012 8. appl., 2013 14 appl.

It should count as +1 in the column "Small Educations" in 2011 and 2012, but not 2013, but it does'nt count at all in any of the Years because the expression adds the applications of all 3 Years together (=31 which is above 10).

How do I correct this?

* "All" means all applications - not all Educations (there is aprox. 70 Educations each year)

Thanks in advance

Cheers

Bruno

[Gysbert_Wassenaar]

Perhaps this:

=count({$<[Education by city]={"=aggr(sum({<Priority={'1. Priority'}>} Number_of_students),Year)<11"}>} distinct [Education by city])

Replace Year with the name of your year field.

[#3]

Hi Gysbert

Thanks for your answer. I can't get it to work though. I have replaced Year with my Year field name (=[Year of appl.]), but all I get is a bunch og 0's

Am I missing something?

Cheers

Bruno

[Gysbert_Wassenaar · Accepted solution]

Nope, it doesn't do what I expect it to do.

Maybe this will work:

count(distinct if(aggr(sum({<Priority={'1. Priority'}>} Number_of_students),[Education by city],[Year of appl.])<11,[Education by city]))

[#5]

That worked perfectly! Thanks a lot

Cheers

Bruno