Hospital challenge (finalist)

The Challenge, the role

Based on a subset of hospital data, the question was to create a high level executive dashboard. With optionally insights. My biggest challenge which I still have not solved: what is the definition of "admissions". The site with the answer for the used system in this challenge, expected me to be a member of the club, if I wanted to dive deep into the standard. I've used the definition, staying in hospital, inpatient as an admission.

Data

I took roughly the following steps to end up with the data model below (all except one calculated column done in PowerQuery):

• group all relevant data from the procedures table (encounters - procedures: 1-n relation) by encounter/patient key. I kept the start datetime from the first procedure and the end datetime from the last procedure. I found out that sometimes it started earlier than the encounter.
• I merged this grouped procedures table into the encounters table. Created adjusted start and end datetimes and calculated the duration. And added some other relevant data I could maybe use, difference between calculated costs (sum base cost encounter + basecosts all procedures) and claimed costs, that kind of columns.
• I thought about the mortality rate as relevant and it is, but I didn't go that way when I saw and example of a patient who died a day later than the date of the death certificate encounter.
• I calculated for every encounter, how old the patient was at the moment of the encounter.
• And yes, I'm very bad at throwing away columns I do not use.

I used Rick de Groot's Calendar script and his offsets were of great use.

Thoughts before starting

• High level executive means a one page dashboard, if possible easy to read.
• Key insights etc, don't belong on the dashboard. The dashboard should just give numbers without interpretation.
• Only year data is a little thin, create an easy way to dive deeper without leaving the screen or adding obvious filtering.
• Create a separate executive summary, next year life can be different and this is a dataset of 970+ patients, followed over the years.

Design

• The upper right corner shows the possibility to somewhat drill down in the way of choosing a Year(default 2022), Quarter and Month view. Year goes 10 years back, quarter goes 12 quarters back and month goes 24 months back. Which in a default setting of 2022 (latest calendaryear with data) should give the results of year 2021, quarter 2021Q4 and month jan 2022 as a KPI with references going back.
• Pressing the level buttons acts like filtering. The buttons are actually a second filter and decide on the viewlevel and periods of trendlines, the first filter (year) is not displayed on the screen but is a single select in the filterpane you could adjust once a year.
• A small info button shows how far you go back and what is what on the trendsvisual.
• In the upper left corner under the logo, the selected timeframe for the viewlevel is displayed.
• Every KPI card has some relevant info + the trendline ending with a dot for the most recent value. Values are all checked or at least, are what I expect them to be. The arrows up/down have a neutral colour. Depends on your goals and the context if some numbers are good/bad.
• Yesterday I borrowed some colours from the Massachusetts General Hospital website.
• All visuals have an y-min value of 0 to make sure differences are shown in proportion.

The visuals are SVG's. Instead of directly connecting the SVG measure to the card image, the card calls a measure called something like __UI SVG Encounters controller and the controller selects the right SVG create Measure for the selected view, returns the SVG image to the controller, who displays it on screen. I was surprised it worked.

Edit 13/7: I corrected 2 mistakes in the quarter view regarding trendlines and uploaded a new image. Total cost quarter boundaries and different pages, year view instead of quarter visual in controller. I'm almost sure another thing can be found in the quarter or month visuals although it would not change the trends in general. In dutch we have a saying "eerlijk duurt het langst". If this were a paid job I would control all these visuals at least 2 times but it's not. At the moment of writing it's weekend, I'm going to sit in my car a few hours and visit my aunt and tomorrow it's wimbledon final + soccer (voetbal) final and then it's time for paid work.

Edit 20/7: Upload report. No changes since last week.