How to use distinct and avg

[Baki]

Hey guys,

I have multiple students from different cities, and I need to list students with the highest grade average for each city and then I need to list the cities from highest to lowest grade average.

Could you please tell me what would the exspression look like?

Thanks

[edwin]

assuming you have the following fields: City, Student, Date, Grade

create a chart, straight table.  Dimension = City; MaxAvg = aggr(max(aggr(avg(Grade), City, Student)), City)

rank:  rank(aggr(max(aggr(avg(Grade), City, Student)), City))

this means get the average for each Student in each City, then get the max per City

hope that works for you

[Baki]

Ok, my bad.

I didn't provide you with enough information. So, this is the whole
scenario.

I need to create two tables. In one table I need to include the
following fields:IdNumber, Grade, UnitID.
In the second table the following fields are required: StudentId, First
Name, Last Name, Address and City.

The above information is created in the excel file on two different sheets.
Once I have uploaded my excel file into the Qlik, I need to create a table
using QlikView and show which city has the best students according to their
grades and list those cities from the highest to lowest.

Hope you'll be able to get the whole picture.

Thanks in advance

[edwin]

The solution still stands.  assuming that IDNumber will be renamed as StudentID (doesnt matter which one is preferred as long as there is consistency), you still aggregate by City and Student first to get the student's average, then aggregate by city to get the maximum per city, then rank it - same expression.  the field names may change depending on preference but the expression is the same

i would think that the data model needs to have some allusion to a date like field ( year and semester most probably)  you dont want to aggregate the whole base so maybe some filtering will be required either outside of the expression where the user selects a period or inside the selection