pie chart using summarized data

[kmstephenson]

Hi All,

I have been having some trouble creating a pie chart to display index hospitalizations that result in readmissions. The pie chart will have 2 slices: "Yes" (if the index hospitalization resulted in a readmission) and "No" (for index hospitalizations that did not result in a readmission). There is one row per patient (pat_id) per rolling year (start_dt and end_dt) per end of life time frame (eol_3month_yn). Index hospitalization and rehospitalization counts are aggregated based on the above structure (See attached excel data structure).

With this data structure is there a way to create a pie chart at the index hospitalization level so that the full pie includes the # of index hospitalizations and the slices are broken into index hospitalizations that resulted in a readmission (yes) and those that did not result in a readmission (no)?

[geert_gelade]

Can you show in Excel the result you want ?

[kmstephenson]

For the above excel example I gave (which only has two patients), I can give you the result I want for a certain time frame. So let's choose an end_dt of 1/31/2014 and eol_3month_yn='T' (these would be selections in list boxes). During this time frame, I can see that patient 1 has 4 index hospitalizations and 2 rehospitalizations. For this same time frame, patient 2 has 3 index hospitalizations and 2 rehospitalizations. So I would want a pie chart at the index hospitalization level, meaning for all patients (only 2 patients in this example) the total number of index hospitalizations would be 7 (denominator) and the pie would be split into two slices- the first slice "Yes" would be populated with the percent & number of rehospitalizations. For this case, that would be 2 (for patient 1) + 2 (for patient 2)=4.  This means 4/7=57% of the pie would be a pie slice "Yes" and "No" would be the rest of the pie (43% because only 3/7 index hospitalizations did not result in a readmission). Does that help?

[geert_gelade]

Is this the result you're looking for ?

[kmstephenson]

Yes that looks great! Is there any way to show the number of index hospitalizations in the pop up for each slice? Currently it only shows percent but I would also like to see a number

[kmstephenson]

Additionally, I noticed that there is no way to select either piece of the pie. Is this something that can be changed? For example, if I want to be able to look at the population with a rehospitalization, I would want to be able to select the Yes slice and have my other charts update to only include patients with a rehospitalization. Is this not possible due to the structure of the data?

[geert_gelade · Accepted solution]

You can solve both "problems" by converting your date in the script. See attached file.

First you convert the basetable to a cross-table. That way you can transform Yes and No to values of the dimension "Type".

[kmstephenson]

With my larger dataset my numbers aren't matching up correctly. I'll play around with it and try to figure out why it's not working. What expression would you add to get the percent in the pop up as well? I thought I could use SUM(DATA)/SUM(TOTAL DATA) but this doesn't work

[geert_gelade]

That expression should work for the pop-ups in %.

[kmstephenson]

due to the way the pie chart is interacting with the other data, we want to restrict/ "freeze" it so that users cannot drill into the slices of the chart. I still want to be able to see % and # in the pop ups. Is there any way to "freeze" or prevent the chart from allowing users to select a piece of the chart?