Create a pivot table
A pivot table is a table that summarizes (aggregates) data in another table and is made by applying an operation (sort/average/sum) to data in the first table. You can create pivot tables using various LogAlto datasets (projects, activities, indicators or form records).
- Create a pivot table
- Configure the pivot table
- Example of pivot table
- Unique count: totals may not fit the content of the table
Create a pivot table
To create a pivot table:
- Click "Analytics" in the main menu (left)
- Click the + sign
- To create the new analytics report, you must:
- Enter a name
- Select a dataset
- Select an output type (in this case: Pivot table)
- Click Save
- A draft data visualization will be displayed
- Configure your report:
- Click the "Toolbox icon" to open the configuration panel
Configure the report to fit your needs
- Click "Generate" see the result
- Click "Save" to save the report
- If you open this pivot table in the future, it will be updated to reflect newly added data. So if you need to keep the report that reflects the current data, you must export it.
Configure the pivot table
In the configuration panel of the pivot table, you will see the following parameters:
Calculation method: Allows to select the calculation that will be applied (Average, Sum, Count, etc.)
For more details, you can read the "Calculation methods" article.
- Field: The field on which the calculation will be applied.
- Add measure: Allows to display (and calculate) a second value.
- Rows: Field that will be used to create the rows/Y axis
- Columns: Field that will be used to create the columns/X axis
- Add disaggregation: Allows to add another level of disaggregation to rows or columns
Distinct value: Allows to count items only once (example: a participant could have attended multiple activities of the same type during the same period. If the rows are activity types and columns are periods, and you need count participations only once per attendee, then you need to select the Attendee field as a distinct value.)
For more details, you can read the "Distinct count" article.
- Totals: When checked, the rows and columns totals will be displayed.
- Filters: Allows to filter out part of the dataset
Example (pre and post survey)
Example: Average grade per gender (pre-post school survey form)
We have a "Students" form and a "Pre-post school survey" form where we collect (among other information) students' grades.
From this form, we want to know the average grade of students (per gender) before and after their participation to our programme.
We will first create a "Blank" pivot table by creating a new analytics report:
- Name: Average grade per gender (pre and post survey)
- Dataset: "Pre-post school survey" form
- Output type: Pivot table.
Then, we will click the toolbox (pencil and ruler) icon to configure our pivot table:
- Calculation method: Average - Check out the other possible Calculation methods here
- Field: Grade
- Rows: baseline or endline
- Columns: Students (Gender)
Unique count: total may not fit the content of the table
When you apply a unique count, the system will only count each item 1 time (for the selected fields).
Example: participants by activity and by gender
There are 3 activities and 8 participants.
As you can you in the image below, the total for the male column does not seem to add up.
This is because one participant has attended multiple activities, but there is a unique count.
If we click on the + sign next to Male, we can expand the category see the male participants:
This shows us that Trent has participated twice (2 different activities). However Trent is only counted once in the total because we applied the Unique Count. If you do not wish to show totals that do not "add up", you can go back in the configuration panel (click on the Pencil and ruler icon) and uncheck the "Show total" checkboxes.
