How to Build Tables that Automatically Show Results from the Latest Period

Introduction

This article describes how to create tables that automatically show the results for the last period - in this example, 4 weeks - but of course it can be any period.

Method

Create the past 4 weeks filter

Create a new binary filter. Select Create > Variables and Questions > Variables > Binary – Complicated Filter.

1. Select Create > Variables and Questions > Variables > Binary – Complicated Filter.
2. Select the Date variable in the Variables and Questions section.
3. Choose Within last period under Contains.
4. Change the Date/Time option to 4 Week.
5. Enter a Name and Label and click OK.

Here is what the filtering tool offers when you select a Date variable.

The options are:

• Within last period – this is the option I will use to select people in the last 4 weeks
• Equals / Not equals – identify respondents from a specific date, or who are not from a particular date
• Greater than / Less than – use these to identify respondents whose dates fall within a particular range

These conditions can be used in combination, with each other, via AND and OR operations in order to customize the range of respondents that will be included.

In the screenshot above I have used the key option that I want to highlight on this post – Within last period. This option identifies the last period by referencing the most recent date among any that are in the data set. So by selecting 4 Week, Q has identified the calendar week in which the latest respondent falls, and it works backward from there to identify the set of 4 most recent weeks. The same applies if you want to identify respondents in the last 6 months, 1 quarter, and so on.

Create the previous 4 weeks filter

Next, I would like to be able to compare results for respondents from the last 4 weeks with the previous 4 weeks. To do that I just need to apply a little logic. To find people from the previous 4 weeks, I need to identify people from the last 8 weeks who are not also from the most recent 4-week period.

1. Select Create > Variables and Questions > Variables > Binary – Complicated Filter.
2. Select the Date variable in the Variables and Questions section.
3. Choose Within last period under Contains.
4. Change the Date/Time option to 8 Week.
5. Press the AND button at the top. This places AND above the filter condition. Any other conditions which are nested below this AND will be grouped together as an AND relationship, meaning that only respondents who satisfy all of them will be included.
6. Press the + button at the top to create a new condition below the first.
7. Change the Date/Time selection to 4 Week.
8. Press the NOT button at the top. This reverses the condition so that it excludes anyone from the last 4 weeks.
9. Enter a Name and Label and click OK.

My setup looks just like this:

Joining them all together

The created variables appear like this in the Variables and Questions tab:

To group them together:

1. Highlight all the rows as I have done above.
2. Right-click and choose Set Question. This is the option to use whenever you want to group variables together.
3. Make sure the Question Type is Pick Any. Q should have this selected for you already.
4. Enter a Name, like “Key Reporting Periods” and click OK.
5. Q will now pop up the Value Attributes window. Check that it looks like the one below, and click OK.

For multiple-response data or binary data like this, the Value Attributes is where you choose which categories will be counted in the table. In this case, Q will count everyone who is in the “Selected” category for each filter (i.e. those people who are included in the filter).

After grouping, the variables should appear like this:

You’ll notice in the Tags column that they are no longer highlighted as filters. If you want to also use these for filtering, just click one of the little yellow ‘F‘s here.

If I use this in the Brown drop-down menu on my table, I get the results from my key periods at a glance.

Next

How To Test Between Adjacent Time Periods