A CrossTab report allows you to quickly add filters and apply hierarchical filters to your variable selection. In this chapter we will explore how to use filters in the CrossTab you have set up.
How to add filters to CrossTab
After adding variables to Rows and Columns you can Generate the table immediately or apply filters to your data limiting the time or variables shown. In the Time Selection menu, you can limit the data by selecting a specific period. By default, it is set to the full period.
The image below shows the time selection menu.
In the drop-down list, you can select from the following (unless they have been hidden in the setup):
- Select start and stop date: Use this option to select a specific date range in calendar controls
- Year to date: This shows all data from the 1st of January (this year) until today’s date
- Full period: Includes all respondents
- Select last: Use this option to select the last X weeks, months etc.
Categorical Filters and Hierarchical Filters
All categorical variables and the hierarchical filters can be used as filters. To apply a filter, simply select the variable by clicking the “Add as filters” button in the panel that appears when you hover over a variable in the list. Alternatively, drag and drop the variable in the filter field. You can either select one at a time or hold Shift/CTRL to select multiple splits at a time.
Step by step
1. Hover and select the variable to be used as filter, by Add filters options
2. Optionally use drag and drop to add in the designated field.
3. Use the select functions of added variable to open the options menu and select answer options for the filter variable you want to use.
Hierarchical filter in CrossTab
If the project contains a hierarchical filter, then it’s filtering the result by default. If the hierarchical filter is removed as a filter, then it will automatically appear as a split in the Row and Column tab. If it’s removed as a split in the Row and Column tab, then it will automatically appear as a filter. This is important because the access rights of the users are based upon the hierarchical filter, so it must always be applied to the calculations, either as a filter or as a split.
Note: The same variable cannot be used as both Question/Split and as a Filter at the same time. If a variable is disabled in the Filter tab, then it is used in the Row and Column tab and vice versa.
Before using the Generate button please note an indicator next to the Generate button that shows the number of cells to be calculated. The purpose of the indicator is to give report users an idea of how big the table will become, as a bigger table means longer calculation time. Also, report users sometimes use nesting option without fully understanding the impact. By indicating the number of cells to be calculated, the table setup can be changed before the table is generated. The image below shows the indicator which is shown in the ‘Generate table’ button.
By clicking the Generate table, the table will be generated. The table can also be downloaded into Excel from this tab by clicking the “Export to excel” button in the top right corner. The thousand and decimal separator used online will be used in the excel exports. If the table shows a period as the decimal separator but you want to use a comma, you can change this in the Settings tab in the Table formatting panel.
In the Generate tab, you can sort the table by clicking the desired column (or row) header of the split that the sorting will be based on. If the questions are positioned in rows, then you can sort by clicking the column headers. If the questions are positioned in columns, then you can sort by clicking the row headers.
Since a table can show the results of multiple calculation types, like the percentage value, the mean value and the correlation analyses value, then you need to define which calculation type the sorting will be based on. You can make this selection in the Generate tab in the top left corner (the list only appears when multiple calculation types have been applied to the table). The image below shows the list you can choose from when selecting which calculation type the sort order will be based on.
The sorting function is disabled if both Numeric and Categorical variables are used as questions in the table. This is due to the fact that different variables use different calculation types, so it’s impossible to create one logical sort order.
- If you have a table with categorical variables and you sort the table by count or percentage share, then the sorting is made within each question if the questions show more than one answer alternative. If all the questions only show one answer alternative, then the sorting is made across all questions.
- When a table that has more than 50 000 cells is generated, you will see a message informing you that time is required to generate the table: “Generating table with more than 50 000 cells could take a significant amount of time.”
Note: “static filters” can be applied to the Cross Table tool always filter out certain data for a user. A static filter is always applied to the calculations and cannot be turned off by the users. Static filters are useful when having respondents in the database that the users of the Cross Table tool shall not be able to include in the calculations for different reasons. It could for example be “incompletes” in project where both completed and incompleted interviews are imported. Another common use case is to provide the users with data for completed months only, even if data is loaded to the project on a daily level, that can easily be achieved by applying static filter to the Cross Table tool.