Rotated databases | Knowledge Base

A rotated database, also called looped or stacked data, stores one record per respondent per repetition of a question, instead of one record per respondent. DataTile can calculate on such data both per record and per respondent. This page explains when data is rotated, how to tell DataTile about it, and what the rotated-data metrics show.

What rotated data looks like

Many surveys repeat the same block of questions for several objects: each respondent rates several commercials, describes several shop visits or evaluates several brands. Such data can be stored in two ways.

Wide (one record per respondent): each repetition gets its own set of columns.

RespID	Gender	Rating_AdA	Rating_AdB	Rating_AdC
R1	Male	Like	Like	Dislike
R2	Female	Like	Neutral
R3	Female	Dislike		Dislike

Rotated (one record per evaluation): the repetitions are stacked, and a respondent appears in as many records as evaluations they made. Respondent-level answers such as gender are repeated on each of their records.

Record	RespID	Gender	Ad	Rating
1	R1	Male	A	Like
2	R1	Male	B	Like
3	R1	Male	C	Dislike
4	R2	Female	A	Like
5	R2	Female	B	Neutral
6	R3	Female	A	Dislike
7	R3	Female	C	Dislike

The rotated layout makes it easy to analyse all evaluations together, for example rating by ad. But the records are no longer respondents: a plain Count of men in this file is 3 (evaluations), although there is only one man.

Records and cases

A record is one row of the file, here one evaluation.
A case is one distinct value of the ID variable, here one respondent.

All regular metrics (Count, Vert%, Vert Base, Mean and the rest) count records. The rotated-data metrics count cases: each ID is counted once per cell, however many of its records fall into the cell.

How to enable rotation

Go to Project settings → Settings → Metrics.
At the bottom of the list, tick Enable rotating.
In the field that appears, type the name of the variable that identifies a case, usually the respondent ID, and select it. Categorical, numeric, date and text variables can be used.

Rotation takes effect only once a variable is selected; ticking the box alone changes nothing. From then on, the metrics below appear in the Metrics menu of crosstabs, charts and dashboards of this database. To turn rotation off, untick the box.

Rotation enabled with CUSTOMERID as the ID variable.

A database has one ID variable. Views created from the database inherit it.

The ID variable decides what a case is. Choosing a respondent ID counts respondents; choosing, say, a household ID counts households. Records with an empty ID are not counted as cases.

Metrics for rotated databases

Metric	Definition	Record-level counterpart
Cases (`rte`)	Number of distinct cases with at least one record in the cell. Weighted.	Count
UQ Base (`ubs`)	Number of distinct cases with at least one record in the column base: in the column, with an answer to the row variable, meeting both rebase conditions. Weighted.	Vert Base
UQ Base (unweighted) (`uub`)	UQ Base without weighting. Off by default.	Unweighted Base
Pen (`pen`), penetration	Cases ÷ UQ Base × 100: the share of cases in the column who have at least one record in the row.	Vert%
Total UQ Base (`tub`)	Number of distinct cases with at least one record that answered the row variable and meets the row's rebase. The column is ignored, so the value is the same in every column.	none
Total pen (`tpn`)	Cases ÷ Total UQ Base × 100: the cell's cases as a share of all cases in the row variable's base.	Base% (roughly)

Filters and splits apply to these metrics as to any other.

Worked example

The seven records above, with Rating in rows and Gender in columns, no weight:

Metric	Male: Like / Neutral / Dislike	Female: Like / Neutral / Dislike	Total: Like / Neutral / Dislike
Count	2 / – / 1	1 / 1 / 2	3 / 1 / 3
Vert Base	3	4	7
Vert%	67% / – / 33%	25% / 25% / 50%	43% / 14% / 43%
Cases	1 / – / 1	1 / 1 / 1	2 / 1 / 2
UQ Base	1 (R1)	2 (R2, R3)	3
Pen	100% / – / 100%	50% / 50% / 50%	67% / 33% / 67%
Total UQ Base	3	3	3
Total pen	33% / – / 33%	33% / 33% / 33%	67% / 33% / 67%

How to read it:

Vert%: 67% of the men's evaluations were "Like".
Pen: 100% of men liked at least one ad, and 100% disliked at least one. Pen of one variable can add up to more than 100%, because a case can have records in several categories.
Total pen: one respondent in three is a man who liked at least one ad.

In a real study the difference is large. In an ad test with 4,514 evaluations by men and 4,445 by women, Count shows those numbers of evaluations, while Cases shows the 2,284 men and 2,206 women who made them.

The same in a database of customer bookings rotated by customer ID. Count is the number of bookings with each status, Cases the number of customers with such bookings, and Pen the share of customers with at least one booking of that status. Pen adds up to more than 100% because many customers have bookings of several statuses.

Count, Cases and Pen on a rotated database.

Things to know

Filters work on records. A filter "Ad = B" keeps only the records about ad B, so a case is counted only through those records. In the example, with that filter Total UQ Base drops to 2, because R3 did not evaluate ad B.
Keep the weight constant within a case. A case contributes the weight of its first record (in file order) in each cell or base. If a respondent's records carry different weights, Cases and UQ Base can use different weights for the same respondent and Pen can exceed 100%. Repeating the respondent weight on every record avoids this.
Regular metrics stay record-level. Mean, Median and the other numeric metrics average over records, so a respondent with three evaluations counts three times.
Significance. Cases and Pen are tested against UQ Base (or UQ Base (unweighted), depending on the weighted-base significance setting). Count and Vert% are tested against record bases, which treats the evaluations of one respondent as independent and overstates the sample size; prefer Pen when testing differences between respondent groups. UQ Base, Total UQ Base and Total pen are not tested.
Settings list. The rotated-data metrics are always listed in Metric settings, but they appear in the Metrics menu only while rotation is enabled.