Introduction
In a table of a Pick One - Multi question type, it is only possible to create a NET and perform merges in either the row or the column, but not both. For example, in the table below, the columns can be merged because they are the codes for the question, but the rows cannot be merged because they are individual questions/variables and essentially separate Pick Ones in the data set.
This is a logical constraint rather than a limitation of the program, as if such merges were possible their meaning would be ambiguous. There isn't a default or standard way of aggregating multiple responses across questions for the same base of respondents.
Consider the consequence of merging the Coca-Cola and Diet Coke rows. When you merge or NET categories, Q computes the result as the proportion of people who choose at least one of the underlying categories. Thus, the Hate score would need to be people who selected Hate for either of the brands, the Dislike score would need to be the proportion that selected Dislike for either brand, etc. The consequence of this would be that the percentages would add up to more than 100%. (as the base for the percentages would still be the number of respondents who answered both questions). Even if a respondent answered Hate for both brands, that would amount to 1 response (n) in the NET/merge, which leads to ambiguous results. This would be confusing as it is inconsistent with the definition of a Pick One - Multi; when the concept of missing data is introduced it would be more confusing yet.
Nonetheless, there are workarounds to this depending on how you'd like to analyze your data.
Method 1 - Convert the Question to a Pick Any
Use this method if you are only interested in aggregating one aspect of the question, such as a Top 2 Box, one specific code, one combination of codes, etc. This example uses the table in the screenshot above to be able to merge and NET Top 2 Box scores across brands.
From the Outputs Tab:
- OPTIONAL: It's recommended you duplicate your question before making edits to preserve the original structure. Right-click on the row or column headings and select Duplicate Question and give it an appropriate name. For example, if your plan is to merge together brands according to whether people love or like them, the name might be Cola brands loved or liked.
- Merge any of the codes that you wish to have merged, such as Like and Love.
- Select all the other codes that you did not merge, right-click and select Hide until you only have a single column left, as shown below.
- A window will pop up to ask if you want to change the Question Type to a Pick Any, click Change to Pick Any.
- Now you can merge the rows.
From the Variables and Questions tab:
- Right-click on the question and select Copy and Paste Question(s) > Exact Copy.
- Change the Question Type to Pick Any.
- Check Count This Value (CHECK) for all of the codes you'd like to count in the final results and click OK.
- Now you can merge the rows in a table for this question.
Method 2 - Convert to a Pick Any-Grid
To retain the original structure of the question, you can convert the question to a Pick Any - Grid. This method is not recommended due to the ambiguous nature of the results. However, it is outlined below in case your specific data setup calculates unambiguous results. Proceed carefully! This example uses the table in the screenshot above.
- On the Variables and Questions tab, right-click on the question and select Ready-Made Formula(s) > Pick One-Multi -> Pick Any (flatten).
- Give the new question a name and click OK.
- Click OK again (nesting by rows or columns doesn't matter since we're making this into a Pick Any - Grid).
- Change the Question Type in the flattened question to a Pick Any - Grid.
- Click OK three times to restructure the data into a Pick Any - Grid.
- OPTIONAL: You can right-click on the columns or rows of this question in a table and select Swap Rows and Columns to get the desired format.
- Now you can merge and NET on both the rows and the columns.
Method 3 - Count Responses Instead of Respondents
Please note that, when using this method, Q's statistical testing system will only ever test the Average statistic, which is not used in the table. As a result, it can be good practice to disable the significance testing by setting Show significance to No in the table.
- Follow the directions in Method 2 to convert your question to a Pick Any - Grid.
- Change the Question Type to Number - Grid. This will make a Not selected response count as 0 and a Selected response count as 1.
- On a table of your new Number - Grid question, right-click and hold down the Ctrl key to check Statistics-Cells > Sum and % Row Share and uncheck Average. The % Row Share will be the % of all responses for the categories included.
- Merge and NET your rows as desired.
Method 4 - Write a Custom Rule for the Table
If you are familiar with writing your own Rules using Table JavaScript, you can create a rule to do one of the following (note, our technical support team is not able to write custom JavaScript rules for you):
- Compute the new combined figures.
- Overwrite the data in existing rows and columns.
- Delete or add new rows or columns to the table.
Note that statistical testing is done before table rules are applied and thus cannot be performed on the figures in the final table.
Method 5 - Create a Custom R Output
If you are familiar with writing custom R code, you can use R to aggregate the data and do the calculations as you wish to create a new R table. Note that statistical testing cannot be performed on R tables.