Rank with multiple dimensions and aggregation

[#1]

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

LOAD * INLINE [
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/2/10, 5/3/10, Advanced, 15%
5/9/10, 5/9/10, Standard, 20%
5/16/10, 5/17/10, Standard, 19%

Thanks in advance

[johnw]

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

[#3]

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.

[johnw · Accepted solution]

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?

[#5]

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.

[Dipak]

Very similar thing i need, but in qliksense.

would you please share formula as i can't open qvw files on my company laptop?