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.
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.
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:
(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
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.
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!