Cohort Grids are the Periscope Data equivalent of Excel Pivot Tables or Two-way Tables. One series is plotted horizontally, and another is plotted on the vertically. A third series of values is filled in for each cell to calculate the sum of their intersecting cells.
A Cohort Grid can have one or more parts. The Cohort column acts as the left-most vertical column of the chart. The Pivot column acts as a horizontal row across the top of the chart. Finally, the data column represents the total of values displayed in the intersecting cells.
Additional "Detail" columns can be appended to the front of the cohort grid from the "Series" tab. If the ratio of the values in the Cohort column to the values in the Detail column is one to many, only the first value within each Cohort Detail pair will be displayed.
Cohort Grids are a great tool to mark the intersection of two or more separate series. One common example is displaying hourly data across days of the week. Shown below is a table where each row represents an hour of the day, the day, and the value at that day.
This three-column table can be converted into a Cohort Grid. The "time" column will act as the Cohort, the "day" column will act as the Pivot, and the "value" column will be the Data. To properly format the Cohort Grid, the Series tab of the Cohort Grid can bet set up using:
And as a result, the Cohort Chart itself now displays the summed data at the intersection of the hour and day.
If a fourth column has been added to the original query that computes the sum of values at each hour, it can be added into the series tab:
Periscope Data also supports nesting multiple cohorts and pivots to produce sums of the "Data" fields per Cohort and Pivot. To induce nesting of categories, two or more series can be selected as a cohort or pivot. Hierarchy of the nesting is determined by the ordering of fields in the SELECT statement, which is also reflected in the "Columns" ordering within the Series tab. Nesting can be performed for both Cohort and Pivot parts, with no limits to the depth of nesting.
With the use of nested cohorts or pivots, each parent cohort line will display the value totaling all nested cohorts below the parent cohort.
Using the arrow indications in the Cohort part of the Cohort Grid, nested hierarchies can be expanded and collapsed as needed.
In the above example, "tiger" within the "america" parent hierarchy is collapsed, and the entire "brazil" hierarchy is collapsed.
More than one field of data can also be plotted for each Cohort and Pivot category. To plot an additional field of data, check another column under the "Data" section. When multiple data fields are checked, these fields will appear as additional Pivot columns within the Pivot part of the cohort grid.
In addition to Table Format Settings found in the documentation here, Cohort Grids offer additional settings as follows.
Coloring is automatically added to the cohort cells depending on their values relative to one another. The largest third of all values are given the darkest blue, followed by the middle third with the light blue, and the bottom third with no background coloring.
This can be disabled by checking the "Disable Cohort Colors" option in the chart format tab.
The "Preserve Sort Order" option orders the Cohort and Pivot based on the specifications within the query.
The "Hide Row Totals" option will disable adding row totals for nested tables.