Comparing two fields in your records: a crosstab without the spreadsheet work

A list of records tells you what each person submitted. It does not tell you how the records relate to each other. Which region registered the most women? Are the late arrivals concentrated at one site? Does attendance differ by session type? Questions like these are answered by relating one column to another, and the standard way is a crosstab, also called a cross-tabulation or a comparison table.

In a spreadsheet you would build it with a pivot table, then rebuild it every time new records arrive. In Pulseform's forms it is a tab on the records page, and it updates itself.

What a crosstab is

A crosstab takes two fields and counts the records for every combination of their values. If the first field is Region and the second is Gender, the table has one row per region, one column per gender, and in each cell the number of records that match both.

(The numbers are made up to show the layout.) Read across a row and you see how one region splits by gender. Read down a column and you see where the women are. That is already more than a total can tell you: the project may have reached 127 people, but the table shows who and where.

Building one

Open the records page of a form and click the Comparative Analysis tab.

1. Choose a field under Analyze By (First Field). This becomes the rows of the table.
2. Choose a field under Against (Second Field). This becomes the columns.
3. Optionally choose a third field under Additional Grouping. The result is split into separate tables, one for each value of that field, so you could look at region by gender separately for each month.
4. Optionally restrict a row's value with Row Filter Value, to look at one slice only.

The Comparison Table shows a count and a percentage for every combination, with row and column totals, and highlights the top pair, the top row and the top column. You can switch between Table View and Chart View with the buttons above it.

It respects your filters

The time filter above the records table applies here too. If you set it to the last thirty days, or to the dates in a field of your own such as a registration date, the crosstab covers only those records. That makes it easy to compare this month's pattern with last month's: change the filter, and the same table recalculates.

Pin it so you do not rebuild it

Once the table is set up the way you want it, click Pin to Analytics. It then appears every time you open the Statistics tab, under Pinned Crosstab Analyses, recalculated with your latest records. You get a permanent view of, say, region against gender, without choosing the fields again on every visit. Click Remove on a pinned crosstab to unpin it.

Choosing fields that make sense

A crosstab works best on fields with a small number of distinct values: a region, a gender, a session type, a status, an age group. Two free-text fields would give a table with a row for every different answer, which tells you little. If you plan to compare records this way, design the fields for it from the start: a dropdown or a set of choices rather than a free-text box, and an age group rather than only an exact age if groups are what you will compare.

What a crosstab does not do

This matters, so it is worth being plain about it. The comparison in forms shows counts and percentages only. It does not run a chi-square test and does not report statistical significance. It is built for exploring your records: seeing patterns, spotting gaps, checking whether a service reached the groups it was meant to reach.

A difference you can see in a table is not necessarily a real difference in the wider population. If you need to say that two groups differ significantly, for a published finding for example, that belongs to a survey, whose comparative crosstab adds the chi-square test and Cramér's V and warns you when the table is too sparse to trust.

For operational questions, such as "did we reach women in the south?", counts are what you need. For a claim in a paper, they are not enough.

Taking it further

• Export the records as a CSV file when you want to analyse them elsewhere. The export contains only what is currently shown, so clear your filters first if you want everything.
• Check data quality first. A crosstab is only as good as the fields under it. Choosing from a fixed list of choices, rather than typing free text, keeps the categories clean, so that "North" and "north " do not count as two regions.

In Pulseform

See view and export form records in the guide for the full steps, and building a form and its field rules for keeping the fields clean.