3 Replies Latest reply on Jan 18, 2019 1:32 AM by Lewis Wong

    Weighted average score by week

    Andrew Hutchinson

      Hi

       

      I’ve got a sheet that needs to show the calculated weighted mean score for customer satisfaction each week - the score is weighted based on the customer group that they belong to. The week is calculated from a date of contact (starting on a Monday) from a date in the format DD/MM/YYYY

       

      Customers fall into three groups:

       

      Group 1 – who contribute 0.5 to the overall weighted mean

      Group 2 – who contribute 0.3 to the overall weighted mean

      Group 3 – who contribute 0.2 to the overall weighted mean

       

      I have three calculated fields to work out the weighted average score:

       

      A “weighted case statement”:

       

      CASE [ServiceType (group)]

      When 'Group 1' then .5

      When ‘Group 2' then .3

      When ‘Group 3’ then .2

      End

       

      This is then used in the next calculated field “Weighted scores”:

       

      [Killer question] *[Weighted case statement]

       

      Killer question (is Q31 in the attached changed from a dimension to a measure) is the measure of customer satisfaction rated from 1-10

       

      The third and final calculated field uses the result from “Weighted scores” and is as follows:

       

      Sum({FIXED DATEPART('week', [Date Of Contact]),[ServiceType (group)]:AVG([Weighted scores])})

       

      This has worked perfectly for a number of months, however on updating the raw data today the overall weighted score is showing as 16 – however the customer scores out of 10. I believe that the calculation is perhaps excluding those dates which are from 2019 when averaging however I don’t know if this is the case or how to check/fix this!

       

      Is anyone able to advise or help on this? I've attached a sample workbook

       

      Thanks

      Andrew