1 Reply Latest reply on Sep 7, 2018 6:03 AM by maneesh.gaddam

    Weighted average by time graph (split by location)

    Andrew Hutchinson



      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 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


      In order to get it into a graph I have (again with some help from the forum!) created a calculated field: Sum({FIXED DATEPART('week', [Date Of Contact]),[ServiceType (group)]:AVG([Weighted scores])}) to allow me to show the weighted average by the date of service.


      The data also has location on it and I need to be able to show a graph of the combined weighted score for all locations as well as the separate scores for each location on the graph. It doesn't matter if this is on separate graphs (so different worksheets) but I am struggling to work out how to get the locations to have an effect on the scores in the graph I have already set up. Filters etc. don't work so I assume I need to add it into the calculated field - possibly using an IF but I can't work out what I need to do!


      Hopefully someone can help me out - I've added a workbook to help explain things better.