Dear Experts,
Kindly, I am looking for a way to present (by using one of the available graphs) the TOP 10 diseases (Number of in-patients by diseases) in our hospital on a quarterly basis, the problem is, if I want to compare between first and second quarters, sometimes a certain diseases is one of the TOP 10 in Q1 and not one of the TOP 10 during Q2, so how can I depict this issue?
Many thanks in advance.

I wouldn't make this harder than it needs to be.

If you are comparing the same diseases from time period to time period, I would use a histogram with paired bars or stacked bars.

If one or more of the top 10 diseases is replaced by a different one, you could use two histograms; one for each quarter.

I would be tempted to add another bar to the stacked or paired bar histogram; one of the original 10 would show the first quarter's figure only, while the 11th would show the second quarter's disease only. It seems to me this would most clearly communicate the trending from one period to the next.

Another option may be to use pie charts.

With a pie chart you could show the top 10 as individual segments and have an 11th segment as others. You can then use a split to give a breakdown of the others. i have attached an example of what I mean as I don't find it easy to explain.

Dear Experts,
Kindly, I am looking for a way to present (by using one of the available graphs) the TOP 10 diseases (Number of in-patients by diseases) in our hospital on a quarterly basis, the problem is, if I want to compare between first and second quarters, sometimes a certain diseases is one of the TOP 10 in Q1 and not one of the TOP 10 during Q2, so how can I depict this issue?
Many thanks in advance.

A different chart for each quarter?

Agree with Jennifer :agree1:
If you are comparing the same diseases from time period to time period, I would use a histogram with paired bars or stacked bars.
....
I would be tempted to add another bar to the stacked or paired bar histogram; one of the original 10 would show the first quarter's figure only, while the 11th would show the second quarter's disease only. It seems to me this would most clearly communicate the trending from one period to the next.

But take in account, staked bars gives you the view to the trend, paired bars gives you the view to the comparison.

Maybe I would use both or add a line trend to the paired bars chart in wich I could add the "stacked" value.

Dear Experts,
I have attached the available data, you can see that diseases F available in quarter one but not available in quartet two, we have a new diseases which is Z.
Kindly, can you educate me, because till know I didn’t get the idea?
Many thanks in advance.

Maybe try using a Pivot Table, using the existing data in the sheet. The Pivot Table can be "drilled down" to specifically identify certain things. It will also change when the data has been modified.

I was playing with the sheet attached, but it wouldn't create a Pivot Table. I will play with it some more, to see if I can create the Pivot Table and attached the file here.

Salman, this is a good website for learning graphs -

http://www.internet4classrooms.com/excel_create_chart.htm

This is the way I see it

Paired bars and "trend" (aka increment / decrement of the particular sickness).

Hi Darius, the graph looks great :)

Use a Pareto Chart style graph. You could have four bars per disease, one for each quarter. The total (over the number of quarters displayed) would be the ranking. If a disease shows up in a quarter but does not rank as one of the top 10 diseases for the year (assuming you did this over a rolling 4 quarters), then it would show up in the "other" category which would be the 11th category.

I think it would be important to track all the diseases that make the top 10 throughout the year. For example, even though "Disease F" doesn't make the top 10 in the second Q, it should still be 1) tallied for the yearly totals and 2) plotted on the chart. This means you may well have to track 15 or 20 diseases to make sure you get the data needed for the "Top 10".

If you don't do this then a couple problems appear
1) Darius' chart implies that there was a 100% change in F & Z, although that is almost certainly not the case.
2) Steven's total ranking for the year would not be accurate. A disease ranked 250 each quarter would come in above a disease ranked 500 for 2 quarters and 150 for 2 quarters (since these would not get ranked in the Top 10 with that value).

I could also imagine cases where a disease ranked steadily at #11 all year would end up in the Top 10 for the year. This would create another challenge to the process.


I took a stab at the graph (inventing numbers for F & Z for the unavailable data). For months where a disease dropped out of the top 10, I added stripes to the corresponding bar. I did this manually, but I bet there is some way to do it automatically.

Tim F

Strange....:lmao:

There are different ways to calculate the difference or Error, the way I learned long time ago is this...

http://www3.ccps.virginia.edu/demographics/magazine/DW%20pages/5_PopupsDW/23P_PercentDiff.html

IMHO, the calculus I did is right, as far as it show what I try to say. The rate of change is relative to the original value, so a -50% is that the new value is the half of the previous value (the same with 100 or -100%).

Strange....:lmao:

There are different ways to calculate the difference or Error, the way I learned long time ago is this...

http://www3.ccps.virginia.edu/demographics/magazine/DW%20pages/5_PopupsDW/23P_PercentDiff.html

IMHO, the calculus I did is right, as far as it show what I try to say. The rate of change is relative to the original value, so a -50% is that the new value is the half of the previous value (the same with 100 or -100%).

Darius, I wasn't arguing with your math - I was pointing out the lack of data (assuming this comment was directed at my comment above).

In Q1, Disease F is #6 with a value of 336. The next month, Disease Fhas dropped out of the Top 10, but for all we know it could well be #11 with a value 190. This would be a drop of about -43%. Or perhaps Disease F has completely disappeared, which would be a -100% drop. We just don't know - unless the data for all the diseases is available even for the quarters when it doesn't make the Top 10.

Tim F

Sorry, agree with the lack of data....:applause:, I missed that point.

A way to do with the chart I gave, is to delete the calculus of the misleading values ( the sickness that weren't in the top ten in both quarters ), so the chart will show a lap without data.

As you said, other way could be to put the value that was the minimum in such quarter (so I can calculate the "at least value"). so F could be at most 235 and the percentage of change could be (235 - 336)/336 >> at least 30% decrease and Z could be (192-314)/314 >> at least 64% increase. And add the text with such legends on the chart. But of course could be better if the missing data could be included.:agree1:

Very nice chart Darius! :applause: It has very nice analytical benefits.

I just made this little piddly set. :o But I will attach them anyway.