Rank top five

[dhborchardt]

I want to pick the top 5 values to present on a chart. I have data from 2005 through 2010 that shows a hospital name and how many events. I want to show the number of events (already summed in the data table) for the top 5 values for each year.

I have played around with the rank function but can't quite get it to work. Do I create an expression for each of the five using rank and setting it to a value which is from highest to fifth highest?

I have attached a simple example of what I have so far. right now the chart shows five hospials that  have picked.

[johnw]

I'm not sure exactly what you're after, but you could do this:

Dimension 1 = Year
Dimension 2 = Hospital
Expression  = if(rank(sum(Events))<=5,sum(Events))

I'd probably make it a stacked bar chart if that's the information you want.

[dhborchardt]

I have an updated qvw attached that shows what I am after. I used Max to find the top five hospitals for each year: "=Max(Events,1)", "=Max(Events,2)". 

This gets me pretty close to what I want. One of the problems with this method is if there are two hospitals that have the same value one will be skipped. For example in 2005 both hospitals 'Eight' and 'Nine' have a value of 5. Only one of these is shown in the chart, the second one is skipped and hospital 'four' with a value of 4 is displayed. Any ideas on how to fix this?

Year	Hospital	Events
2005	One	3
2005	Two	6
2005	Three	7
2005	Four	4
2005	Five	8
2005	Six	3
2005	Seven	2
2005	Eight	5
2005	Nine	5

The chart should show the top 5 hospitals in this order:

Five /8 events

Three /7 events

Two / 6 events

Eight /5 events

Nine /5 events

The second thing I still need is to label each bar with its hospital name. How do I get the legend to display the corresponding hospital name?

[johnw]

This?

Dimension 1 = Year
Dimension 2 = =aggr(if(rank(sum(Events))<=5,Hospital),Hospital) // suppress nulls, sort by sum(Events) descending
Expression  = sum(Events)

See attached.

[dhborchardt]

That works great for one year. When more years are added it has a problem. It look at which hospitals have the most events total and only shows those as the five examples. In 2006 it shows hospital Eight =4, even though hospital Six=6. I need it to calculate each year. I am guessing that the legend will be a problme with this scenario and am trying to figure out a way to remove the legend and display the hospital name on the bar instead or in addition to the event value.

[johnw · Accepted solution]

OK, so you want the highest ranked hospitals for each year, not the highest ranked overall.  Yes, the legend and how you want that sort of data displayed could be a problem.  I don't think we can use a calculated dimension in this case, because with that approach, a hospital is either included or not.  If you do what I suggested in my first reply (but without stacked bars), you get the correct bars for the correct amounts, but there are blanks for the missing bars and the sort order is overall, not per year.  You can take an approach like you came up with yourself, but since the hospital ranks differ by year, there's no good way to handle the legend that I can think of.

As a completely different approach to displaying the information, see the attached.  I'm using a straight table with a gauge chart pretending to be the bar chart.  I use background color expressions to distinguish better between the years.  I show the year, hospital name and the # of Events next to the bar.  It's certainly not what you're asking for chart-wise, but it seems to contain the necessary information in a fairly clear format.

[shaunsomai]

Have you tried doing this in a mekko chart  do you have qlikview 10.

something like this

Basically having and expression sort, of sum(events) descending and on the presentation tab  set max visible no  to 5 on the hospital dimension

Updated below

[IAMDV]

John - I am following your posts. And the new approach of ranking is fantastic and personally to me, this is best way to visualize the data. Thanks again for your expert suggestions.

Cheers - DV

[dhborchardt]

Thanks. Your method of displaying in a straight table will work great.

Dale