# Rank with multiple dimensions and aggregation

Hi I'm really struggling to get this to work. What I need to do it to group several jobs by day and then report out the highest abandon rate by job/ day over each week.

I've added an example where I can aggregate the jobs by type and by date, workout the overall ranking, and display just the one with the highest ranking, but I can not get this to work over multiple week. In the example there are 3 weeks, so there should be 3 rows displayed (highest rank for each week), but there is only one.

Here's the data

Week, Date, Type, JobNumber, Placed, Abandoned
5/2/10, 5/2/10, Standard, 123, 100, 10
5/2/10, 5/2/10, Standard, 124, 100, 10
5/2/10, 5/3/10, Standard, 125, 100, 14
5/2/10, 5/3/10, Advanced, 126, 100, 15
5/9/10, 5/9/10, Standard, 127, 100, 30
5/9/10, 5/9/10, Standard, 128, 100, 10
5/9/10, 5/10/10, Standard, 129, 100, 14
5/9/10, 5/10/10, Advanced, 130, 100, 15
5/16/10, 5/16/10, Standard, 131, 100, 10
5/16/10, 5/16/10, Standard, 132, 100, 10
5/16/10, 5/17/10, Standard, 133, 100, 19
5/16/10, 5/17/10, Advanced, 134, 100, 15
];

Table output should be:
Week, Date, Type, AbandonRate
5/9/10, 5/9/10, Standard, 20%
5/16/10, 5/17/10, Standard, 19%

• ###### Rank with multiple dimensions and aggregation

I'm totally trial-and-erroring here due to lack of experience with firstsortedvalue(), but this SEEMS to work:

• ###### Rank with multiple dimensions and aggregation

Thanks for the reply John. I though that was it, but I need to display the Number Placed and Abandoned as well. This requires having sum(Placed) and sum(Abandoned), the firstsortedvalue function does not appear to allow aggrogates, and if two groups come in first does not appear to give any value.

• ###### Rank with multiple dimensions and aggregation

Aggregations appear to just require an aggr() function. So you can use the firstsortedvalue() approach for your sums as well. I just used max() because it was simpler for that case. Here's an example doing every field the same way. Mind you, I bet there's a better way. But at least this is A way. EDIT: Or not, since as you say, it doesn't return anything if two groups come in first. But what do you WANT to happen if two groups come in first?

• ###### Rank with multiple dimensions and aggregation

Thanks a lot again. It's giving me the results I want, even though the help in QlikView says aggregated functions are not allowed, so I'm very impressed.

Ideally, if 2 jobs have the same abandon rate I would like to report both (having 2 results for the same week), but that is something I can live without as the data sets are much larger than in the example and unlikely to have the same figure.