How to combine Pivot Tables to avoid double-counting beneficiaries
The following section describes how to use two Pivot Tables in ActivityInfo when you analyze the collected data to avoid double counting an indicator for an activity. It shows how to create a Pivot Table based on the data of another Pivot Table.
Double counting means counting the same person (e.g. beneficiary) reached by an organization more than once for example in the same reporting period or by more than one activity. Double counting inflates the count of people reached.
If for example you did a distribution of Food relief items to 200.000 beneficiaries and Non-food relief items to 300.000 beneficiaries in a population of 400.000 people, then adding up the beneficiaries of the two activities wouldn't be correct. Some beneficiaries got both types of items so they shouldn't be counted twice.
To avoid double counting a beneficiary when you are doing data analysis in ActivityInfo, you can use two Pivot Tables. One Pivot Table will show the MAX numbers for the indicators (i.e. beneficiaries) you have selected and the other Pivot Table will summarize these numbers.
You could for example first find the maximum number of beneficiaries reached by projects, by district, and then sum those districts together in a second pivot table to find the total by province.
The following example contains dummy data but is based on the real structure of Forms used for WASH activities by the Yemen WASH Cluster.
Before you start designing the Pivot Table, add Codes to the fields you want to use. This will make it easier to use them in the Formula editor.
- Click on Reports to navigate to the Report List page and click on "Add pivot table".
- Click on the arrows to reveal the contents of the Databases and Folders and tick the boxes next to the Resources you want to use to select them. When you are ready click on "Done".
- In this example, we want to use the Records of a Monthly Subform which is used to collect information for Beneficiaries getting assistance under various Activities. The Activities are included in the Parent Form.
- Next, we can add our Measures and Dimensions. You can directly drag and drop your Quantity fields in Measures. But if you want to use a more advanced structure, you can use the Formula editor and add a Calculated field.
- In this example, we want to add as Measures the Quantity fields of Beneficiaries reached (as reported in the Subform) for each Activity in our Form. To do this we will use a Calculated field.
- Because the Records of the Beneficiaries belong to a Subform, we will navigate to the section where the fields of our Subform appear and we will click on "Add calculated field."
- In the Formula editor we will add the Formula which will allow us to bring in the Total Number of Beneficiaries for each Activity. The Total Number of Beneficiaries is a Quantity field and the Activities field is a Single Selection field.
- To make the Formula easier to create we have added Codes to every field in our Form. The Code for the Total Number of Beneficiaries in our Form is "BENE_TOTAL".
- The Code for the Activities is "Activity" plus the ID of each option in the Single Selection field.
If you don't see some of the Fields or the Options click on the downward arrows to reveal more contents.
- The Formula in this example will be:
"IF(Parent.Activity.ck2u7tw63a, BENE_TOTAL, 0)".
This is an IF Formula that will bring as a result the Quantity that appears in the field with the Code "BENE_TOTAL" when the ID of the Activity is equal to "Parent.Activity.ck2u7tw63a". Otherwise, it will give as a result "0".
- To start writing the Formula, type in the Formula editor "IF(". Then, click on the first option under the Activity field (in this case it would be "1. Provide operational support to water supply system"). Finish the Formula by typing ",0)"
- Click on "Done" when you are ready.
Read more about Formulas in ActivityInfo.
- The first Measure has been added and you can view it in Measures.
- Give it representative name and from the statistics list deselect "Sum" and select "Max".
- This will give you the Maximum quantity available for this Measure.
- Let's add some more Measures in the same way.
- Navigate to the section where the fields of the Subform appear and click on "Add calculated field."
- In the Formula editor type the same Formula as before but instead of using the first option under the Activity field, use the second one. In this example, we would click on "2. Provide spare parts and maintenance for water supply systems".
- Click on "Done" when you are ready.
- You have successfully added another Measure. Give it a descriptive name in the Measure card, deselect the Sum Statistic and select the "Max" statistic to show the Max quantity available for this Measure too.
- Continue adding more Measures in the same way.
- In this example, we have added as a Measure the MAX number of Beneficiaries available for 4 different Activities.
You might want to get the SUM for a specific indicator instead of the MAX because you know for example that each Beneficiary must be counted. In that case instead of checking the MAX statistic, you should check the SUM statistic. This will show the SUM for that field instead of the MAX.
- Now let's start designing the Pivot Table to make more sense out of our data.
- Drag and drop the fields you want to use as Dimensions to Rows. In this example we will use the Governorate, the District and the Month.
- Move the Measures to Columns.
- When you are ready click on "Save report" and give a descriptive name to your Pivot Table.
- After creating the Pivot Table which includes the MAX values of your indicators, navigate to the Report List page and click on "Add pivot table".
- We will now sum up the MAX values we got on the first Pivot Table.
- This time we will use as a Resource the Pivot Table we created.
- Click on "Reports" to reveal the available Reports and click on the Report you created to select it. When you are ready click on "Done".
- Drag and drop to Measures the fields that we created in the previous Pivot Table which show the MAX values for that Measures.
- Click on each Measure and make sure that in the Statistics, the "Sum" statistic is selected.
- Start designing the Pivot Table in the way you wish. In this example, we will add the Months and the Governorate in Rows.
- Make sure you move the Measures to Columns.
- Click on "Save report" and give a descriptive name to your Pivot Table.
- You can remove a Dimension to customize the Pivot Table to your needs.
- In this example, we will remove the Governorate. Click on it in and click on "Delete".
- The Governorate Dimension is removed and you can view the maximum values of the selected data summarized by Month.
- Finally, you can add a Calculated field to show the SUM of these values.
- Click on "Add calculated field" and write the Formula brings in the MAX for the Measures.
- Select SUM as a statistic.
- Give a descriptive name to the Measure and save your Report.