I work in a hospital. I have a data table (set) that includes many fields, but among them are date of service and diagnosis. Each row in the data set represents a patient encounter (visit) at the hospital. I'm wanting to compute a variance in the count of patient encounters between dates of service in 2017 and dates of service in 2018 to see if the occurrence of diagnoses have gone up or down. I have experimented with value field settings of "difference from" between column 2018 and "base item" of year/column 2017. However, if I then want to sort by the largest variance to smallest it makes me either sort on column 2017 or 2018 (without the value field setting) and then it does not give me the desired result. I feel like I am missing an easy step that I just have not learned yet. Is there any suggestion to do this? Thanks, Jeff
Hey Jeff,
You need to put the Patient Name or Diagnosis in the Values Area, this will give you a Count of these occurrences.
Then put the Date Field in the Column Labels and either the Patient Name or Diagnosis in the Row Labels (drag this from the Field List).
Right Click on the Pivot Table values and select: Show Values As > Difference From
BASE FIELD = Date
BASE ITEM = (previous)
Let me know if this help.
If it does not, please attach your sample workbook and take out any sensitive info.
Cheers,
John
I guess I figured it out. I think I'm still in the mindset of presenting data versus analyzing which is what a pivot table is for. Also is there a way to do a regional zip code heat map in Excel 2016 from a pivot table or is that a more advanced power pivot, etc. type thing? Should I keep trying to figure it out or punt?
Hi Jeffrey,
I don't see why you can't do a heatmap with a PivotTable similar to this: https://www.myonlinetraininghub.com/excel-heat-maps
If you're stuck, please start a new topic for the heatmap and include a sample Excel file so we can give you a specific example.
Mynda
