Verticalized by List exports

The Deliverables applet provides users with several different file types for exporting reports, including Verticalized by List exports. Verticalized by List reports take a single list from the survey to display vertically ("verticalized") in order to analyze it alongside the list's related survey fields. Then, responses are displayed in a verticalized output with a single column for a question and multiple rows for each respondent record. Only survey fields that are associated with the selected list are displayed vertically. All unrelated fields will be displayed in their standard output.

The Verticalized by List export differs from the Verticalized (Tableau) export in that it is driven by the selection and verticalization of a single list, as opposed to questions. A side-by-side comparison of these two reports can be found in Verticalized data and reports.

Understanding the concept

In general terms, "verticalized" data is data that is presented in a vertical orientation where the survey question/variable is displayed in a single column in a report. Multiple rows exist for the question, where each row represents a new element related to the question. For example, a multiple select (checkbox) question with five answer choices would be presented in a single column with five rows, one row for each option. Verticalized data, then, expands by adding more rows until all the data is represented within the single column. This is the opposite of the more common approach of displaying a single survey question across multiple columns, e.g., "non-verticalized" data. This data is displayed horizontally and expands by adding columns to the right for each additional element or related data. 

In IntelliSurvey's software, the term "verticalizing" means to take a survey question that would normally be displayed in multiple columns and instead display it in a single column. For example, a single select (radio) table asking about "likelihood to recommend" for three brands would normally be displayed with one row and three columns, where the single row represents the respondent, and each column represents one of the three brands. Meanwhile, in a verticalized report, the same table is represented by one column and three rows, where the column represents the table, and each row represents one of the three brands. 

Since single select questions are displayed in a single column by default, the option to verticalize is not applicable. Therefore, verticalizing data is not simply "all the data appears vertical." Rather, it is the action of changing the data format from horizontal to vertical, where relevant.

Verticalized by List output for checkbox QS31

Non-verticalized standard output for checkbox QS31

Creating a Verticalized by List export

To create a Verticalized by List export, click on Deliverables under the Reports button group in the Navigation menu. Next, click on the option New Export in the blue menu bar to open the Create Exports screen. Then, select Verticalized by List (xlsx) from the File Type dropdown menu in the Export Options section. This automatically brings up the By List Selection pane. Users will need to choose from the List menu, which defaults to displaying the list that is most frequently used in the survey. Lists in the menu are shown in descending order from most to least frequently used.

Note, all survey fields (questions, variables, content iterated within blocks/loops, etc.) associated with the selected list are automatically included in the export. For example, if a the list GENDER is used at Q1 in the survey, then Q1 will not appear as an option in the Field Selector because it is already included in the report.

Once a list is selected to build the export around, any additional fields required for the report should be selected. Note the Field Selector is already set to Individually when Verticalized by List (.xlsx) is selected, and the user can add individual fields to the file as needed. The Record Selector can also be used to choose which respondents to include. By default, it is set to Completed.

Note, all additional fields are exported with the same data output they have in a standard Excel export, as opposed to the fields included by default, which have a verticalized presentation. Take the example below. SODABRANDS is the list selected for the analysis. QS31, a checkbox question, uses this list in the survey; therefore, it is included as column 'QS31' by default and is verticalized. Then, QA2, also a checkbox question, was manually added in the Field Selector because it does not use the same list SODABRANDS. Notice this question is presented in a multi-column output because this is the standard output for this question type.

After the additional fields have been added, users can include additional export options by expanding the Advanced Export Options section. By default, Expand stacked fields is checked.

Once all export options have been selected, click Next to continue.

This brings users to the final screen of the Create Exports applet where users will see the results of the file being created. If the file is successfully created, users will have the option to download the file, save the export, or both. Note that the Save Export button will not be active unless a file name is entered in the Description field. If need be, users may alter the exports settings via the Adjust file button at the bottom of the screen.

Once the export has been downloaded and/or saved, users may elect to create additional exports via the Create another Export or Adjust file buttons. To return to the Deliverables home screen, click either Done, or the Deliverables button in the menu bar.

Verticalized by List output

Verticalized by List exports are outputted as an Excel (.xlsx) file. The Excel file has a Data tab as well as a Questions tab that maps the codes to the question and option text in the survey.

SPSS (.sav) files will be available in future releases. 

Report layout

Below is an example of a Verticalized by List export. The following columns are added by default:

• id - The record_id with the appended option ID from the verticalized list.
• record_id - The unique respondent ID.
• LISTNAME_index - The list selected in the By List Selection pane.

The first two columns in the report show the respondent ID in two formats. The record_id column is the respondent ID, also known as the trans_id. Meanwhile, the id column is the respondent ID with an appended option ID that matches the option code from the verticalized list. If the first option in the list SODABRANDS is coded as '11', as shown above, then record_id TEST40003 becomes id TEST40003-11.

The third column in the report is the index for the verticalized list. The index is a list of codes that correspond to each option in the list.

Following the first three columns, all survey fields associated with the selected list will appear in the order they are programmed in the survey code. If applicable, a survey field will be presented in a vertical manner. For example, column QS31 represents a checkbox question and is presented vertically. Respondent TEST40003 has a value of '1' for SODABRANDS option 11 because it was selected. SODABRANDS option 12 has a value of '0' because it was not selected. The next column, QS32, is a radio table. SODALIST option 11, which was previously selected at QS31, is given a response of '2' (according to the Questions tab, this is "familiar but never tried it"). However, SODABRANDS option 12, which was not previously selected, is blank because it was presented to the respondent. 

The additional fields that were selected in the Field Selector appear at the end of the file, to the right of the last associated question. Just like other reporting applets, the survey fields will appear in the order the questions were selected. Columns QB3R1 through QB3R3 represent a radio table, and columns QA2.1 through QA2.4 represent a checkbox question. Since these survey fields are not associated with the SODABRANDS list, they are not verticalized and instead follow the standard data output. These columns along with QS11, a radio question, simply repeat the answers as many times as needed for the respondent.