Count of things with given conditions AGGR()

[charlesdmeyer]

     I have been using this equation to help me find the number of students that missing more than 5 days of school in a text box on Qlikview.

=count(DISTINCT if(aggr(count(gaattDailyDetail.date),[Student Name])>5,[Student Name]))&chr(10)&'Students missing more than 5 days'

This formula gives me a different answer depending on the school that I choose from the drop down box.  It works great and provides me with exactly what I need.  However, my needs have changed. 

I want to create a text box for each school with this formula in it that is stagnant and doesn't change when I select a different school.  In others words I want to create a sheet of 16 of these with the exact number of kids missing more than 5 days at each school.  I can't figure out how to use my dimension "School" to force the formula to only look at "Ron Burgandy Elementary" in one text box and "Paul Pogba High School" in another.

Any help would be appreciated.

[ahaahaaha · Accepted solution]

Hi Charles,

May be like this

=count({<[YourNameFielfScool]={'Paul Pogba High School'}>}DISTINCT if(aggr(count(gaattDailyDetail.date),[Student Name])>5,[Student Name]))&chr(10)&'Students missing more than 5 days'

And so for the Text box for every schools

Regards,

Andrey

[swuehl]

I think you  need to apply the set expression to the inner aggregation, too (or at least to the inner one):

=count({<[School]={'Paul Pogba High School'}>}

     DISTINCT if(aggr(

               count({<[School]={'Paul Pogba High School'}>} gaattDailyDetail.date)

               ,[Student Name])>5,[Student Name]))

&chr(10)&'Students missing more than 5 days'

Set Analysis in the Aggr function

[charlesdmeyer]

Thanks to both of you for your help. 

[charlesdmeyer]

Stefan,

I have seen lots of your answers to others and they are always exactly what I need, but here is another question.  In this case I want the number of students at Paul Pogba High School who have been tardy or have checked out for the day.  I used a similar formula like the one you helped me with before, instead of [Student Name]>5, I used [Attendance Status]='T'.

I think it has something to do with the 'T', because it will count all the students out on a given day but it won't eliminate the non tardy students from the count.

Should I be using a different bracket notation around the 'T' or something.

Thanks.

[swuehl]

How does your complete expression look like and how do your records with the status look like (and how are they linked to student)?

[charlesdmeyer]

It looks like this...

count({<[sch.name]={'Paul Pogba High School'}>}DISTINCT if(aggr(count(attendanceDetail.date),attendanceDetail.status)='T',student.personID))

The 'T' is for Tardy, in the same column of the data is an 'E' for Check Out.  Right now, when I use this, it counts every kid that was out, it does not distinguish between 'T' or 'E'

[swuehl]

attendanceDetail.status is the dimension of your aggr() function, not the expression, which you want to compare with 'T'.

It seems like you are basically comparing a count of dates with 'T'.

I wonder though why this returns anything at all.

Maybe something like this:

count(

{<[sch.name]={'Paul Pogba High School'}>} DISTINCT

     aggr(

          If(attendanceDetail.status ='T',student.personID)

          , student.personID

     )

)

Not really sure if you need the advanced aggregation at all, maybe this works as well

=Count(

     DISTINCT 

     {<[sch.name]={'Paul Pogba High School'}, attendanceDetail.status = {'T'}>}

student.personID)

I've made some assumptions about your model, so maybe none of these expressions work.

[charlesdmeyer]

As always, you are correct with your function help.  Thanks so much