1 Reply Latest reply on Mar 12, 2020 2:35 PM by Adrian Zinovei

    Having Trouble Calculating Survey ResultResponse Rate

    Rachel Sarabia Ortiz

      I am trying to create a dashboard that displays count of respondents, count of people surveyed and a response rate. Currently, I am using a filter and a parameter to make this work. I cannot figure out the calculation I need to make the response rate work correctly using just my filter on college.

       

      Can someone walk me through what I might be missing, what I can do to make this calculation work? Thank you in advance for your time and your help.

       

      Total Number of Respondents- Calculated using the following LOD:

      { FIXED [College Location Primarily Work],[ResponseId]: [Total Numerator (copy)]}

      Total Numerator Calculation:

      SUM({ FIXED [ResponseId]: MIN([Weight])})

      Weight Calculation:

      case [College Location Primarily Work]

      when " College 1" then 1.170816

      when "College 2" then 0.790835

      when "College 3" then 0.901423

      when "District Office" then 0.648912

      else 1

      END

      Total Number of Employees Surveyed- Calculated using the following LOD:

      { FIXED [College Location Primarily Work]: MIN([Total Denominator])}

      Total Denominator Calculation:

      IF [College Location Primarily Work]= "College 1" then 1379

      ELSEIF [College Location Primarily Work]="College 2" then 323

      ELSEIF [College Location Primarily Work]= "College 3" then 337

      ELSEIF [College Location Primarily Work]= "District Office" then 122

      ELSE 4 (these are the respondents who did not identify which college they were from)

      END

       

      Response Rate- Calculated using the following LOD:

      SUM([Numerator LOD])/SUM([Denominator LOD])

      Response Rates were not calculating correctly. I wanted Response Rates to be the following:

      College 1 29.9%

      College 2                      29.7%

      College 3 30.0%

      District Office            29.5%

      Unknown                   100.0%

      Grant Total                30.0%

       

      So, I created the following calculation:

       

      Response Rate with College Filter

      CASE [College Location Primarily Work]

      WHEN "College1" then [C1 Number of Respondents- Weighted]/[C1 Number of Employees Surveyed]

      WHEN "College2" then [C2 Number of Respondents- Weighted]/[C2 Number of Employees Surveyed]

      WHEN "College3" then [C3 Number of Respondents-Weighted]/[C3 Number of Employees Surveyed]

      WHEN "District Office" then [DO Number of Respondents- Weighted]/[DO Number of Employees Surveyed]

      ELSE [Unknown Number of Employee Respondents]/[Unknown Number of Employees Surveyed]

      END

       

      The Response Rate was still not calculating correctly for the Grand Total- Which Represents All or District-Wide Results.

       

      So, I created the following parameter:

      And used the following calculation to get the response rate above:

       

      Response Rate with Parameter

      CASE [Parameters].[College Location Primarily Work]

      WHEN "All" then [Weighted Total Number of Employee Respondents (copy)]/[Weighted Total Number of Employees Surveyed]

      WHEN "College1" then [C1 Number of Respondents- Weighted]/[C1 Number of Employees Surveyed]

      WHEN "College2" then [C2 Number of Respondents- Weighted]/[C2 Number of Employees Surveyed]

      WHEN "College3" then [C3 Number of Respondents-Weighted]/[C3 Number of Employees Surveyed]

      WHEN "District Office" then [DO Number of Respondents- Weighted]/[DO Number of Employees Surveyed]

      ELSE [Unknown Number of Employee Respondents]/[Unknown Number of Employees Surveyed]

      END