Editing a Query

To edit a query, click the Queries tab of the Survey page, then click the Edit link next to the name of the query you want to edit.

After clicking the edit link, the Query Edit page will open.

Editing a query involves selecting a list of variables, and optionally applying criteria for filtering, sorting, cross-tabulation, time periods and submit status. The Query Edit page provides access to each of these items.

Choosing Variables

To include a variable in query results, simply click on the variable in the All Variables list. Each variable selected appears in the Selected Variables list to the right.

Variables will appear in the query result in the same order they appear in the Selected Variables list. To re-order variables in the Selected Variables list, click on the name of the variable, then click one of the arrows to the right of the list.

NOTE: When using the tools on the right of the list, only one variable can be moved at a time.  It is a best practice to select the variables in the order they should appear in the Selected Variables list.

To remove a variable from the list, either uncheck the box next to the variable name in the All Variables list, or select the variable in the Selected Variables list and click the delete button to the right of the list.

Shortcuts to Add or Remove Variables

The All Variables list is divided to into sections, each of which represents a collection. It is possible to select all of the variables in a collection by clicking All in the blue bar to the right of the collection name. De-selecting all of the variables in a collection can be achieved by clicking the Clear link in the blue bar.

• Clicking All at the top of the list selects all of the variables from the entire survey.
• Clicking Clear at the top of the list removes all variables from the query.

Seeing Test Data

It is possible to query test data instead of real participant-submitted data. Test data includes any data submitted by test participants (who usually work with the survey designer during the design process), or any data generated by the Data Manager’s Add Random Test Data feature.

The properties section at the top of the Query Edit page tells you whether Test Data will be included in your query results.

To show test data in the query results:

1. Click the Properties button at the top of the query page.
2. Check the box Use Test Data option.
3. Click Save.

Sorting

Raw query results can be sorted by any variable that appears in the results. Sort Variables can be defined before running the query by checking the Sort box and selecting a variable from the Sorting Variable list.

It is possible to choose more than one sort variable.

To add a secondary sort, click on the Add link and select another variable by which to sort. The top sort will be the primary sort, with subsequent sorts applied afterward.

 

To remove a sort, click on the Remove link.

 

The sort direction can be Ascending or Descending. “Ascending” is the default, and it will sort text alphabetically, a through z. Numbers will sort from lowest to highest. Dates and times will sort from early to late. If “Descending” is selected for Direction, the sort will be reversed.

Cross-Tabs

When defining a cross tab variable, Illume breaks down summary results according to scale values of the cross tab.

For example, if the survey includes a question called GENDER, and it is selected as the cross tab variable, then the query results will show response data for males and females side by side.

To apply a cross tab variable, check the Cross Tab checkbox below the All Variables list, then choose the cross tab variable from the Cross Tab Variable list.

Cross-Tabs and Preloaded Data

Pre-loaded data can be used as a crosstab variable only if the preloaded item has a scale (a defined set of possible values). The person designing the survey decides whether the preloaded data will have a scale.

Submit Status

By default, the query will return results from both completed, partial and terminated surveys. A completed survey is one in which the participant clicked the final Submit button. A partial survey is one in which a participant may have answered some questions, but left before clicking the Submit button. A Terminated Survey is one that has “Terminate” set in a Jump Object, and has been exited out of the survey based on Jump-If logic. Data can be limited in the query to include either Partial Submissions, Completed Surveys or Terminated Surveys by following these steps:

1. Check the Submit Status checkbox.

2. Under Submit Status, check Partial Submissions, Completed Surveys or Terminated Surveys. Any combination can also be selected.

NOTE: If no box is checked, partial, completed and terminated submissions will appear in the results.  The only way to restrict results to a single submission type is to check only one of the boxes.

Filtering

A filter or condition can be added to a query to limit the results that are returned by the query. The user defines filters by selecting the variable to filter, the operator to apply to the filter, and the value to be used by the filter. The result is in the form of “Variable Operator Value”.

Filtering and Yes/No Questions

When applying filters to Yes/No questions, there are some special considerations.

Yes/No questions include both individual checkboxes and check all that apply questions.

 

These questions can have three possible values in the dataset: Yes, No, and Unanswered.

• A value of Yes indicates the participant saw the question and checked the box.
• A value of No indicates the participant saw the question and chose not to check the box.
• A value of Unanswered indicates the participant never saw the question. This is often due to show-if logic.

If a survey with 1000 respondents includes a conditionally displayed checkbox question, the responses may break down as follows:

Yes	No	Unanswered
600	300	100

In a query that includes a filter on this question, the number of responses that pass through the filter will vary depending on the Operators selected.

Filter	Results	Comment
QUESTION Is 1:Yes	600	Includes only those who saw the question and checked the box.
QUESTION Is 0:No	300	Includes only those who saw the question and did not check the box.
QUESTION Is Not 1:Yes	300	Includes only those who saw the question and did not check the box.
QUESTION Is answered	900	Includes all who saw the question, whether they checked the box or not.
Question Is unanswered	100	Includes only those who never saw the question.

 

In some cases, it may be interesting to see responses from everyone who did not answer Yes to the question. In this example, 400 participants did not say yes: there were 300 explicit Nos and 100 who never saw the question. To get results for these 400 participants, you need two filters:

• QUESTION Is 0:No
• QUESTION Is unanswered

These filters must be joined with an OR in the Logic section. The final filter to retrieve these 400 results looks like this:

Filtering Unanswered Questions

When applying a filter like this to a query:

 

STATE Is Not : FLORIDA

It would be expected to see results from all participants who did not indicate Florida as their state of residence.

In fact, the results would display participants who said they live in some state other than Florida, but will not display participants who never answered the question about what state they live in. For participants who did not respond to the STATE question, DatStat Illume cannot definitively determine whether or not they live in Florida, so it excludes them from the results.

If the query results should include everyone except those who explicitly stated that they live in Florida, the following two filters must be applied:

STATE Is Not Florida

OR

STATE Is unanswered

 

NOTE: The two statements use “OR,” not “AND.” This ensures that anyone meeting either criterion appears in the result set: either they explicitly stated that they do not live in Florida, or they did not answer the question.

Advanced Filtering

The real power of query filtering comes from adding multiple filters and logically organizing the filters to retrieve narrowly targeted results. For example, two filters can be applied to the same variable to ensure values fall within an expected range. When two or more filters are used, it is possible to AND the results (where all filters are true) OR the results (where either filter is true). This causes the query to return the intersection or union of the individual filters, respectively.

If more than two filters are used, further power can be gained by logically arranging the filters in an expression. For example, it is possible to retrieve results where the first filter is true and either the second or third filter is true.

In the example above, this query will find all participants in grade 10 or above who report being sick or injured at least 3 times.

Complex Expressions

To write complex expressions:

• Notice that each filter has a letter ID, beginning with A and proceeding through the alphabet. Use the letter IDs to represent filters in your expression.
• Operators in an expression include AND, OR, and NOT.
• AND generally limits the number of results, since conditions on both sides of the and operator must be true for data to pass the filter.
• OR generally expands the number of results, since only one of the conditions in the OR statement must be true to pass the filter.
• NOT negates a condition.
• AND is evaluated before OR if they are on the same level of the expression.
• Use parentheses to set the logical structure of the expressions. Nested expressions are evaluated first.
• A letter can be used in an expression multiple times (A and B) or (A and C) for example.
• It is not necessary to refer to ALL filters in the complex expression. In the above example, it is OK to write (B or C), essentially ignoring the A condition.
• Expressions must be well formed.

This notation is the same as that used in creating complex show-if conditions in the Survey Designer.

Time Period

When a survey has multiple time periods, the query can filter the data by time period. The default (no time period selected) will not filter the data and data from all time periods will be returned.