This content has been marked as final. Show 2 replies
I've created a weighted score from a set of service types - service type 1, 2, 3, 4, 5 and 6 and people are asked to rate their satisfaction with the service they receive for this out of 10. The data is set up as follows:
The service types form part of a wider group - so service types 1,2,3 all fit into the category of "washing", service types 4 and 5 are "drying" and service type 6 is "ironing" each combined service type group contributes to an overall mean score - however each group has a different weight when it comes to the make up of that mean score. So - washing counts towards 50% of the average score, drying 30% and ironing 20%.
With some help from the forum I have created this weighted score using a weighting case statement:
CASE [Service received]
When 'Washing' then .5
When 'Drying' then .3
When 'Ironing' then .2
From there I have then created a calculated field to apply it to the overall satisfaction score: (([Score for service])*([weighting case statement]))
So then I have scores that show the weighted score for each area e.g. Washing 4.393, Ironing 1.450, Drying 2.250
I need to be able to create a total score (which is fine if I want to display in a table - I just use the analysis "Show column total" and "Total using Sum"
However I need to also create a graph of just the overall average - so in this case - 4.393+1.450+2.250 = 8.093, as the data is collected weekly and we want to track the scores over time and create a trend graph. I've tried this on sheet 2 of the attached workbook but it just displays the individual scores on the graph.
I feel like it should be quite straightforward to do but I can't think of how to do it! I've attached the example workbook.