4 Replies Latest reply on Dec 11, 2012 9:53 AM by jacob perler

# Create a Calculated Field and subsequent column for the difference between two groups of data

I am modeling some survey data and I grouped the respondents into two groups.  Essentially, if 40 people took the survey there are 2 groups which aggregate the results of 20 people for each group.  Their answer choices range from 1-4 for every question, and so I set up my data table as the column being each respondent (2 columns appear since the 40 respondents are in 2 groups), and my rows are the questions they answered.  The output is a table with the average response in text format of the 2 groups for each question.

I would like to create a third column which is a "gap analysis," so essentially it is the difference between groups 1 and 2.  I've been looking into creating a calculated field but am having some trouble since I don't see the groups as an option to calculate in a formula.

Does anyone have any idea how to create a simple gap column to be the difference between the two current columns?

Thanks!

Jake

• ###### 1. Re: Create a Calculated Field and subsequent column for the difference between two groups of data

Jacob, hard to tell from you description what exactly you're trying to do, so two comments:

1. Go here and follow the link to the best way to visualize survey data via Joe Mako.
2. If that doesn't motivate you change your viz, then post a packaged workbook with some sample data.

--Shawn

• ###### 2. Re: Create a Calculated Field and subsequent column for the difference between two groups of data

Shawn,

Thank you for the reply.  Please see the attached packaged workbook, and specifically, the last tab titled "Operations Diagnostic."  Essentially, I am looking to create a third column which is the difference between the 1st column and the second column (i.e. a gap analysis).  Does this clarify it a bit?  The first column has an alias but is one respondent, and the second column is a grouping of respondants.

Thanks,

Jacob

• ###### 3. Re: Create a Calculated Field and subsequent column for the difference between two groups of data

Hi Jacob,

There are a couple of ways to do this. One way is to add a Difference Table Calculation to Avg Response (right click on in on the text shelf and select Quick Table Calculation-->Difference. Then, place another instance of Avg Response on the view. (Refer to Sheet8).

Another option is to create a couple of calculated fields. First, a calculated field will need to be created to group (Q1_1)1#Your Name (Question) into the two types (an ad hoc group cannot be referenced in a calculated field). Then, for each of the two groups a calculated field will need to be created. (Refer to Sheet7).

Hope one of these options help!

-Tracy

• ###### 4. Re: Create a Calculated Field and subsequent column for the difference between two groups of data

Tracy,

Thank you very much, this is very helpful!  While the first option is definitely easier, the second option will likely help me with some more complex versions of this issue.

Thanks again,

Jacob