topic Re: Rank top five in QlikView

Rank top five

dhborchardt — Thu, 07 Jul 2011 16:01:08 GMT

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.

Re: Rank top five

johnw — Thu, 07 Jul 2011 21:42:34 GMT

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.

Re: Rank top five

dhborchardt — Fri, 08 Jul 2011 13:21:07 GMT

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?

Re: Rank top five

johnw — Fri, 08 Jul 2011 19:33:44 GMT

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.

Re: Rank top five

dhborchardt — Mon, 11 Jul 2011 20:30:23 GMT

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.

Re: Rank top five

johnw — Tue, 12 Jul 2011 19:39:10 GMT

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.

Re: Rank top five

shaunsomai — Tue, 12 Jul 2011 20:18:21 GMT

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

Re: Rank top five

IAMDV — Wed, 13 Jul 2011 08:17:47 GMT

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

Re: Rank top five

dhborchardt — Wed, 13 Jul 2011 19:26:36 GMT

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

Dale