2 Replies Latest reply on Jan 13, 2020 6:04 AM by Ciara Brennan

    Creating metric calculations from pivoted survey data

    Don Sievert

      Trying to wrap my head around the best approach for creating metric calculations from data input values that have already been pivoted.  I have a large survey (Hundreds of columns) that represent a number of different question types (likert, Multiple choice, data input, etc) and I used some of the excellent tips from others on the forum and online for pivoting the data and adding helper files.  Great Stuff!!!


      However, after getting through visualizing a number of the values I realized I have to also create a number of calculated metrics from the input values that have already been pivoted into 2 columns:  e.g. Question id and Question Value. 


      Visualizing the actual input values is easy but I am having difficulties trying to create a calculated metric from the pivoted results.  For example: if I need to calculate Avg Hours per Volunteer: I need to sum total volunteer hours / total volunteers.  A pretty easy calculation when each metric is its own column.  However, once the data is pivoted I need to create the logic to select only those rows that are applicable.  I usually need to show N, Median, Avg descriptive values.  I'm finding some of these rather difficult as the numbers are not coming out the way I would expect.  


      What is the best practice and does anyone have any good examples I can borrow before I go further down the long trial and error path?


      1. Create the calculated metrics before you pivot the survey data?

      2. Create 2 datasets--one pivoted for Likert and other data types and one table for non-pivoted input values used in calculations?

      3. Use the appropriate LOD for calculating the Avg, median, N, upper and lower quartiles? 


      Sample of Pivoted Data:

      Provider     Demo a     Demo B          Question ID                         Question Value

      Provider 1     A               A                    A5 (No Volunteers)                   150

      Provider 1     A               A                    A10 (No Hours)                        7700



      Thanks in advance