3 Replies Latest reply on May 4, 2012 3:20 PM by Jonathan Drummey

    Group respondents for averaging responses

      I'm new to Tableau (trialing it).  I am trying to analyse results from a tracking study where we have 1,000 survey responses from 50 individuals.  Each response is tagged by a RespondentID and a date/time of the event.


      I want to be able to report on the average number of visits per respondent per week. 


      How can I create a calculated field that groups the RespondantIDs then counts them.


      I think I would use a table calculation for this, but am really not sure.  Any advice or instructions would be appreciated.


      Thank you in advance.

        • 1. Re: Group respondents for averaging responses
          Tracy Rodgers

          Hi Joel,


          It depends exactly how the data is set up. Would you be able to post a sample/example?


          However, a calculated field can be created to group together the RespondantIDs into groups of your choosing and then, using the count function can be used to find the total. From here a table calculation--using the total function can probably be used. Just make sure the Compute Using is correct (right click on the calculation on the view and select Compute Using...)


          Hope this is of some help!



          • 2. Re: Group respondents for averaging responses

            I think I understand.  I've attached a simple data file.  The respondentID shows 10 respondents.  (Of course they wouldn't normally respond sequentially...)  So each entry is "owned" by one respondent who is reporting on a separate occasion.


            The question I want to answer is: What's the average number of visits/responses per respondent per unit of time?  (In reality this will be per week or month, for this sample we can just use hours for simplicity.)




            So it's group by RespondentID

                   Count each group

               Then report the average as the average number of visits per unit of time.



            I apologize if I'm not saying clearly or using the correct terminology. 



            • 3. Re: Group respondents for averaging responses
              Jonathan Drummey

              Hi Joel,


              I was looking through some forum posts from the past week and found yours. Here's a solution that uses the following table calculation:


              IF FIRST()==0 THEN

                  WINDOW_AVG(SUM([Number of Records]),0,IIF(FIRST()==0,LAST(),0))



              The IF and IIF statements are to make sure that we're only returning one value for each date, the key bit is the WINDOW_AVG(SUM([Number of Records]). With RespondentID in the view, this will ultimately make Tableau sum up the number of responses for each RespondentID, then the Compute Using will set the calculation up to have a "window" or partition of all responses for a given time period defined by the discrete (blue) time pills in the view.


              You'll need to make sure the RespondentID is somewhere in the view (the Level of Detail shelf makes a nice place), and set the Compute Using for the table calculation to RespondentID. The attached workbook shows this.