Press Ganey percentile ranking

[Anonymous]

Looking for advice on how best to approach calculating percentile ranking on the fly in Qlik Sense. The Physician Performance Scorecard.qvw made available on the Community website in 2014 doesn’t make the conversion to percentile ranking.

Press Ganey supplies me an updated conversion chart twice a year. Note there is a different conversion Chart for each question on the survey. We want to compare ourselves to all of Press Ganey’s book of business, so loading that detail to Qlik Sense doesn’t make sense.

In Qlik Sense I load both the numerator and denominator to calculate the Top Box score (for my providers) with the following formula:  Avg(LTR_SumOf5s) / Avg(LTR_SumOfn)

Currently I’ve built a nested IF statement in a Master Item to make the conversion to Percentile Ranking. The downside to this method is maintaining the Master Items as there are 6 Master Items in each app and there are 15 apps. The nested IF statement is also 100 layers deep.

I’ve looked at using a Pick(Match()) but it doesn’t support >= (only 😃 so I had to cover all 1000 possibilities. This would also be difficult to maintain.

I’d like to be able to load the conversion chart in a table (maybe an island), so all I’d have to do is add it to my load statements. Unfortunately I have not come across a method to use a table for this conversion as the Top Box score is calculated on the fly.

Appreciate any help you can offer.

[joe_warbington]

Hi Carl,

Have you explored using the function Fractile() for this purpose? Fractile - chart function ‒ Qlik Sense

There are a number of posts on Qlik Community with examples of this and how to calculate rankings like quartiles and deciles.

[Anonymous]

Hi Joe,

I looked at the Fractile function a while back and it appeared to calculate a percentile ranking if you had all the detail records. We use the percentile rank across Press Ganeys complete book of business and those detail records are not available to us. Press Ganey does supply a conversion chart (for each question) from the “top box” score I calculate in the app.

I’m attaching a dumbed down and PMI free copy of the Press Ganey section of the app. Note I’ve built the conversion chart into 6 Master Items (2 questions and then provider, division and department levels for each question).

I’m also attaching a copy of a conversion chart we get from Press Ganey. Each question is a different page.

Appreciate any help you or anyone else can offer.

[sarahplymale · Accepted solution]

This is a bit late, and not an elegant solution, but you could replace the hand-keyed numbers in your expressions with variables, and then read in the variables from a common spreadsheet that you can then update 2x per year.

This code will bring create a variable from a spreadsheet of variable names and scores:

Temp_Variables:

LOAD

    VariableName,

    TopBoxScore

FROM [lib://AttachedFiles/Book1.xlsx]

(ooxml, embedded labels, table is Sheet2);

for i = 0 to NoOfRows('Temp_Variables') - 1

  let vName = peek('VariableName', i, 'Temp_Variables'); // Name of the variable

  let $(vName) = peek('TopBoxScore', i, 'Temp_Variables'); // Number of the variable

next i

let i = null();

let vName = null();

drop table Temp_Variables;

You can then repeat this for the percentile scores.  You would end up with 200 variables (100 for the TopBox score and 100 for the corresponding percentile), but at least it is reusable from one app to another and easier to update.

There has to be a better way, but I'm not coming up with it right off.

Sarah

[Anonymous]

Hi Sarah,

Thank you for taking the time and energy to submit this solution. Your are correct, it's not as elegant a solution as I was hoping for but it seems well thought out. I've already made the updates for this period, but will keep this solution for future use if nothing better comes along.

Appreciate your help!

Carl