Product Tour
forums
Use these forums to ask questions and discuss Tableau.
Showing Top 10s by a Dimension
Posted January 9th, 2008 by Dan Gerena
in Calculations
Let's say I have data that shows a record for each emergency room visit, and in that record I have the Age of the patient (rounded to nearest whole number) and the Diagnosis (there are 500+ unique possible diagnosis codes)
I want to show in a tabular fashion the top 10 by Age in one single worksheet. Thus the top 10 (based on COUNT of patients) for 2 year olds will be markedly different than the top 10 for 15 year olds.
I know how to do this for a single age (by simply puttign the age in the filter), but when I try to do thsi for ALL ages, I get a top 10 of the entire population for each age, instead of each age's respective top 10.
How can I do what I'm attempting?
Comments
Hey Dan,
If i understand you correctly, it sounds like what you want to do is:
1. Put the Age field on the Rows shelf. Make sure it is a Dimension. You can convert a Measure to a Dimension by dragging it from the Measures area of the Data window to the Dimensions area. (see Understanding Data Types and Roles. to learn more).
2. Then drag the Diagnosis field to the Rows shelf as well (place it just to the right of the Age field).
3. Then Drag the Diagnosis field to the Filters shelf.
4. In the Filter dialog box, click the Top tab and select By Field.
5. Specify Top 10 by Patient Count and click OK.
Now your view should show for each age, the top 10 diagnoses. You could then add in whatever other measures you want. For example, you could put the Patient field on the Columns shelf and then select COUNT as the aggregation. That way you can seethe number of patients that were given each diagnosis.
Another thought I had is that if you are working with a lot of ages you could put the Age field on the Pages shelf instead of the Rows shelf. Doing so would make it easy to advance through each age rather than try to have them all in a single view. However, that would make it difficult to compare the most common diagnoses between ages (e.g., 2 year old diagnoses to the 3 year old diagnoses). I just depends on the questions you're trying to answer.
Thanks for the reply Erin. I had actually done what you suggested prior to my posting the question, but the issue is that those steps show the top 10 as defined for the ENTIRE population, and not the top 10 for each Age.
In my underlying data, the top 10 for a 1 year old differs than the top 10 for a 15 year old, but the steps above just simply repeat the same 10 diagnoses regardless of the age. For instance, I expect to see a high prevalence of "Fever" for infants, but broken bones for 15 year olds. But my data shows the same 10 Diagnoses for each age.
Screen shot attached
There are two option to do this:
1. Create a sheet for each age group (make sure age group is on the filter shelf and right click to 'add to context') and lay them out together on a dashboard.
2. Create a quick filter for age group (make sure age group is on the filter shelf and right click to 'add to context', 'show quick filter', on the quick filter pull-down to 'select single value'). This will allow you to see the top ten for each age group one at a time.
Would there be a 3rd option of creating a calculation to rank the diagnoses segmented (or partitioned) by Age (so each Age has their respective diagnoses listed as 1 thru 500)?
If such a calculation is possible, then I would just apply the filter to the calculation to limit it to the first 10 for each age.
Hi Dan,
Right now the 'Top 10' is always for the entire context. You can't do it per Age value. We do not yet support creating a view like you are looking for, though we understand the value of such a thing. We intend to do this at some point in the future, but we are trying to figure out the best way to expose it. The SQL is often inefficient. For now, the best way is probably to use the quick filter mechanism erin proposed.
:)ross