Selecting a certain rank

[danielact]

In my dataset, let's assume I have a table with teh following fields:

Department

Year

Loss_#

Loss_Amount

For each year, there can be any number of losses. Let's say there are 100 years of data. I want to see, by department, the 10th worst year.

I thought this would work:

top(aggr(sum(Loss_Amount),Year),10)

For some reason, unless I put year as my dimension, it won't work - it comes up blank. Not only that, if I put in only year as the dimension and then put in this as an expression:

rank(aggr(sum(Loss_Amount),Year))

I'm getting values over 100. Shouldn't the highest value be 100?

Ultimately, I want to see the 10th worst year's total losses by department.

[swuehl]

Hi danielact,

I think top() is not the appropriate function (top is an inter record function, not the function to deliver top 10 value).

With out tryingmyself, try something like

= only({<Year= {"=rank(sum(Loss_Amount),Year) =10"}>} Year )

for #10 worst year (but not by department, right now)

If you post a small example, someone in the forum might help you, almost for sure.

Regards,

Stefan

[swuehl]

Hi Danielact,

I played a little bit around, maybe I have found a solution. Please have a look at attached qvw.

The expression uses firstsortedvalue to get the the Xth worst year per department and shows corresponding  Loss_Amount.

Is this something you had in mind?

Stefan