6 Replies Latest reply on May 14, 2012 6:12 AM by Emma Whyte

# How to rank individual based on satisfaction scores?

Hello,

I am working with a set of data collected from a satisfaction survey where respondents scored Speakers at events on a 1-7 scale.

What I am trying to do is create a ranking system for these speakers.

We have created a variable in a MySQL database called Speaker Satisfaction, which is the average of the averages of answers to multiple questions about a Speaker. We did this because the survey did not include an Overall Satisfaction measure.

I have already searched on the forums and found related posts. I have tried the formula suggested by Joe (below) but this does not seem to be working for my data?

Formula used currently:

IF NOT ISNULL(AVG([Speaker Satisfaction])) THEN

IIF(AVG([Speaker Satisfaction]) == LOOKUP(AVG([Speaker Satisfaction]), -1),

PREVIOUS_VALUE(1), INDEX(), 1)

END

I would really appreciate anyone looking at the workbook attached and seeing if they can figure out where I am going wrong.

Thanks

Emma

• ###### 1. Re: How to rank individual based on satisfaction scores?

Hello Emma,

Your problem can be easly solve I guess using the INDEX function. I think you don't need all the tests you've added. Instead you should simply:

- create a calculated field "Rank" having Index() as formula.

- sort by "Speaker Satisfaction Average".

If you have 22 different speakers (hence 22 rows in your table), the calculated field Rank will range from 1 (first row) to 22 (last row).

If you sort by speaker names, A will be ranked 1 and V will ranked 22.

If you sort ascending by Speaker Satisfaction, A wil be 1 and V 22 but N will be n° 2.

If your sort descending by Satisfaction, n°1 wil be V and n° 2 will be S.

Now if you'd like something more permanent, my guess is that you'll have to use some sql queries.

Hope this helped,

David

• ###### 2. Re: How to rank individual based on satisfaction scores?

Hi David,

Thanks for the advice. What I'm looking for is something that is more permanent like you say. This data will be updated every month with new satisfaction scores, meaning that rankings will fluctuate frequently.  Therefore I would really want something that will update itself, without me having to manually re-rank every month and re-upload the workbook.

What SQL queries did you have in mind?

Thanks

Emma

• ###### 3. Re: How to rank individual based on satisfaction scores?

Hi Emma,

The solution I proposed will actualize. You won't need to re-rank every month once you set up the sort of satisfaction field. Just actualize your datasource.

You can easily simulate it by entering new data in your excel file, update your data source and check the result.

Concerning the sql statement, it was a quick thought. Now that I think a bit more about it, I don't think you'll need any.

David

• ###### 4. Re: How to rank individual based on satisfaction scores?

Hi David,

Thanks for the info

Can you define what you mean by "actualize your datasource"?

Emma

• ###### 5. Re: How to rank individual based on satisfaction scores?

Hi Emma,

Sorry if I wasn't clear. I meant "refresh the data source".

David

• ###### 6. Re: How to rank individual based on satisfaction scores?

Hi David,

Thanks for your suggestions. With a bit of playing around with the Table Calculation I have achieved my desired result. Here is my method:

1. Create calculation called Speaker Ranking:

Index()

2. Use Advanced table calculation:

3. I then used a Size() calculation in the table as well to give me the total number of Speakers from a string field.

Thanks again - hope this helps someone else as well!

1 of 1 people found this helpful