Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
georgina_spary
Contributor II
Contributor II

Aggregating averages -- aggr()?

Hello folks

I am really stuck on this and was due to release the report today! so really hoping you can help.

The attached shows an average score for students, against a heirarchy of dimensions: student number, programme, department, faculty. Each student has only one score. Each student is studying 2 programmes: BI and one other.

I am trying to avoid double-counting the students at Faculty level while showing an accurate figure at programme and student level. see attached for 2 failed versions.

Thanks in advance

George

2 Replies
swuehl
MVP
MVP

Maybe like this?

=Sum(Tariff)

/ Count(aggr( [Student Number]*[Valid Tariff Count],Faculty,Department, Programme, [Student Number]))

edit:

Above is probably not what you want, I missed the part with the not-counting duplicate Student Numbers.

If you rework your data model so that the student number's tariff is a distinct table, I think your version A should match your requirement.

Please check attached.

georgina_spary
Contributor II
Contributor II
Author

Thanks swuehl for coming back to me so quickly.

You are quite right but in an attempt to explain the problem I oversimplified my example. The link table is actually v large and I'm already loading it distinct. I will have another go at giving a more accurate example and get back to you.