Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Dalton_Ruer
Support
Support

CMS Data via Data Market

Since launching a few weeks ago the folks at our fictitious General Hospital health system have been quite busy. General Hospital

They were one of the first to purchase the CMS data set from Qlik Data Market and jumped right into the analysis. They discovered that they could quickly see their reported scores and then do a few other clicks to compare to anything and they were happy campers. For awhile anyway.

It gets old clicking, writing down numbers and then comparing in your head. But what could they do?

GHScores.png

They blocked aside some time and began catching up on the Healthcare Webinars OnDemand.   After watching the session on Set Analysis they realized they could make their lives so much easier by adding another expression into the table to see the average for their state.

StateAvg.png

When I saw their expression I was little overwhelmed because it's a lot to swallow.

StateAvgExpression.png

I had to digest the expression in chunks and will help explain each piece to you as well but if you are unfamiliar with Set Analysis and the AGGR function be sure to click this link and watch the On Demand Tech Thursday webinars for those.

The heart of Set Analysis is that you can define exactly what you want. So the first part of the expression Hospital_Name=E(Hospital_Name) simply says "Use any hospital name except for the one that I've actually selected." So that General Hospital's (or your hospitals) scores aren't included in the average.


They realized that while they were an Acute Care Hospital not all others were. They didn't want to compare themselves to averages for unlike hospitals so they added the next piece of the expression Hospital_Type=P(Hospital_Type) to tell the Set Analysis to only use other hospitals that matched their hospital type. (Click here to see more details on the Exclude and Possible E()/P() functions.)

Once they figured out that they could tell Set Analysis to look at the Possible values they added the final touch so that they were only comparing to the scores for their state State=P(State).

Thus the Set Analysis piece was actually pretty straightforward once it's broken into pieces.

Avg({<Hospital_Name=E(Hospital_Name), Hospital_Type=P(Hospital_Type),  State=P(State) >} Score)

But what they saw after they created the expression was pretty heartbreaking. No values for any of the rows. After scratching their heads for awhile they realized something very critical about Set Analysis ... it is only calculated 1 time per chart and not for each dimension in a table like they had hoped. In other words it doesn't know to calculate for each of the Measure Names.

That's why understanding the AGGR function comes in so handy. It will do the calculations as a matrix across the Measure Names and then they could pull out the averages for each measure.

Avg(Aggr(Avg({<Hospital_Name=E(Hospital_Name), Hospital_Type=P(Hospital_Type),  State=P(State) >} Score), Measure_Name))

Having the numbers correct for each measure was a big win for them. Again short lived though.

Because they got tired of having to do the mental math comparing the numbers. Not only did they have to compare the numbers some of the measures are in # of days, not in rates. So while higher rate numbers are bad, the higher number of days is actually good as it means patients didn't return for longer. So they decided to add some coloring to make it easier for end users to consume. By doing that they were able to eliminate the confusion for users. Notice that their Readmission Rate for Heart Failure is lower than the rest of the state (green) and the number of days before readmission is considerably higher but it also shows green.

StateAvgCompareColor.png

Once they were cooking with gas they decided to tackle comparisons for the rest of the country as well. Was that hard? Not a chance. All they had to do was remove part of their Set Analysis formula that said only use my state.

Avg(Aggr(Avg({<Hospital_Name=E(Hospital_Name), Hospital_Type=P(Hospital_Type) >} Score), Measure_Name))

NatAvgColor.png

I'll bet you think they moved on to something else after accomplishing this. But you are wrong. They realized this type of comparison was good but they also wanted to be able to compare themselves to specific other hospitals and they sure weren't going to tell someone to write numbers down and then click another hospital and write those numbers down.

Lucky for them Qlik supports Alternate States. Meaning you can create multiple sets of filters that are independent of the others. They are built right into QlikView and that's what General Hospital is using primarily so I will document how they did it in this post. Qlik Sense also supports Alternate States but you need an extension to handle that. I've created a video that illustrates which extensions to get from Qlik Branch and how to utilize them to do the same things within Qlik Sense: Alternate States in Qlik Sense - YouTube

They figured from a high level that it would be good to have 3 different sets of selections so they created 3 Alternate States.

AltStateCreation.png

Once they had the 3 Alternate States created they created 3 sets of Multi Selection Boxes and Current Selection objects and tied each to the corresponding Alternate State.

AltStatesSelection.png

Now they were able to manually define any comparisons that they wanted to make to get the complete context of their particular scores. In this image they compared themselves to other Acute Care Hospitals in Pennsylvania that are non profit of any type and against Voluntary non profit Acute Care or Critical Access hospitals around the country. They created a single selection for Measure Name so that they could select any single measure across all of their selections.

AltStatesMore.png

The work to get the chart to show the values for the various Alternate States is actually easier than you may think. If you guessed that somehow you needed to use Set Analysis you are correct. The key is understanding how to refer to each state. If you have played with Set Analysis in the past you are likely aware of the 2 most common starts

{$} - Use the default selections that have been made

{1} - Ignore the selections that the user has made

When using Alternate States you can start the same way and simply name the Alternate State you wish to use

{NameOfAlternateState}

In this case the General Hospital data architect simply created 3 formulas

Avg({State1} Score)

Avg({State2} Score)

Avg({State3} Score)

Easy breasy. There was just 1 problem. No matter what Measure Name was chosen the chart showed the exact same values.

Normally I would make you guess, but I'll save you the drama. The set analysis expressions as written said "ONLY look at values that are filtered from the various states, thus ignoring the DEFAULT SELECTION STATE. They could have added Measure Name to each Alternate State but since it wouldn't make any sense to compare one rate to another rate they opted to do what they did, simply use a single Default State selector for the measure name. Now the problem is "how do you then tell the set analysis expression to use it?"

Turns out it's pretty easy you simply use the format "$::" to represent the default state like so:

Avg({State1 <Measure_Name=$::Measure_Name>} Score)

They got fancy and added the reference line to show the National Average for the measure in the chart so that all end users could see how close to the average each set of selections was. The only thing they didn't like was that users still had to read the numbers and that the variances between each as they were close wasn't all that obvious. They thought of changing the axes for the chart not to start at 0, but then remembered they would probably get their hand slapped by the Qlik Dork if he ever saw them playing with the axes.

So they opted instead to use the national average as the 0 line instead of showing it as the reference line and simply changed the expressions for each bar to subtract the national average:

Avg({State1 <Measure_Name=$::Measure_Name>} Score) - Avg(Score)

The result is a simple way that still allows end users to consume the context for the values. The General Hospital rate is 1.88 higher than the national average while the peer groups are very close to the national average. In the first chart 1.88 is barely reflected but visualizing in this manner enables the differences to be displayed instead of asking people to do the mental calculations.

AltStatesNatCompare.png

If you like the first way better that shows the actual values, you aren't alone. That's why the designer at General Hospital put the button in the application that allows end users to toggle between the views for the information they want.

I hope you enjoyed this particular writing style. I figured it would be fun to pretend that General Hospital actually existed, and it would be easier to word how powerful Set Analysis, AGGR and Alternate States are in this manner. Now for the truth ...

If you do decide to purchase the CMS DataMarket data set it already comes with applications that include all this. But even if you don't you know have some ideas of how to add value to your other applications by adding this kind of value.

1 Reply
richardouellett
Creator
Creator

Thank you for sharing Dalton!