00:00:00 Introduction and agenda

For the introductions and welcome once again, everyone. This marks the second session of our Calculated tables webinar series. For those who were available last week, and for those who have followed the recordings of the session from last week, we had our first session where we delved into the fundamentals of Calculated tables for data analysis. The recording is also made available in the chat of this session today. We'll address questions that you may have and possibly show an example or a question we received from another customer before the session.

On our agenda today, we have a question that pertains to understanding how Calculated tables work—basically, understanding where they go. I will deal with that question first. Subsequently, we would address your questions to clarify any gray areas that you encountered while you practiced, and questions that might pertain to specific use cases that we will be presenting afterwards. We will showcase an example of a practical scenario that answers the question of how to calculate progress against a target, and finally take any remaining questions that you may have and conclude today's session.

00:01:39 Where does the calculated table go?

One major fundamental question concerns what happens when you create a Calculated table. In other words, the question is: where does the table go after you exit the formula editor? Firstly, let's refresh our memory by establishing that once you take the action of clicking on 'Add Calculated table' in ActivityInfo, it takes you to a blank formula editor.

This editor requires you to provide a label for the table and then the formula that pulls together all the data that you want to combine to create the new table. This transformation provides you with the Calculated table containing a list of as many fields as you created. The newly calculated field appears in the report design pane under the label that you have provided. However, leaving the field in this report design pane does not do much for your analysis.

Moving on from that step, you proceed to drag the fields from the report design pane either as a measure, a row, or a column dimension. The drag-and-drop action creates a table and a chart, or if you decide to change the format to a graph or whatever display suits your need. Then you can also perform calculated measures, or even further Calculated tables, using the fields that you see on the left.

00:03:56 Practical scenario: Calculating progress against target

We have a worked example based on a question that a user had posted to us. In this scenario, the goal is to calculate and evaluate the progress made towards a specific target across different quarterly reported metrics, such as the direct beneficiaries, indirect beneficiaries, reported facilities, supported facilities, and distributed items. Imagine that you are managing this program and the reporting requirements for the analysis involve presenting reports to donors and displaying the quarterly performance against predefined targets.

To accomplish this, a structured approach is put in place. It is suggested to leverage functions such as SELECTCOLUMNS for data selection, SUM for aggregation, and ADDCOLUMNS for additional computation. The strategy involves selecting, aggregating, and calculating the cumulative sums across the quarterly reported data. This process will enable you to evaluate the performance of actuals against predefined targets, supporting the creation of reports that offer analysis and visualization of progress made towards the set targets.

00:06:57 Database structure and setup

Let's begin by understanding the database we are using. First, we have a reference folder containing the country reference form with countries like Uganda, Kenya, and Philippines. We have the form with the indicators; two are for the implementing organization and two are for the donors. We also have the projects or locations where these projects are taking place. For this, we have the country MEAL plan which contains the target and the baseline for each country. Finally, we have the data collection form, which is an indicator reporting form reporting actual targets.

It will be important for us to do a backcasting to visualize the table that we are about to create. In this table, we have the indicator name, the Target as a column, the Baseline, Quarter 1, Quarter 2, and Quarter 3. We also have the cumulative of the actuals from the three quarters, and finally, the calculated field which is the progress to Target value.

00:10:16 Creating the calculated table

To calculate this, we go to the reporting form, click on 'Analyze', and add a pivot table. We then click on 'Add Calculated table'. We give it a label, for example, "KPI Report". We will use SELECTCOLUMNS, ADDCOLUMN, and SUM, and later use a calculated measure to pull the average of the progress to Target onto a new column.

We start by inputting the formulas. We use SELECTCOLUMNS to select the indicator progress form, reporting month, country name, indicator name, target, and baseline. We want to get the accumulated sum, so we create a column for the actuals.

For the quarterly breakdown, we want to calculate the sum if the reporting month falls within specific quarters. For Quarter 1 2023, we use SUM combined with IF statements checking if the reporting month is January, February, or March. We then replicate this logic for Quarter 2 (April, May, June) and Quarter 3 (July, August, September), adjusting the month numbers accordingly.

00:16:24 Explaining the formula logic

To explain the formula, SELECTCOLUMNS selects specific columns from the data table including the progress report, country name, indicator name, target, and baseline. It creates a new column, the cumulative, that sums up these actual values. This represents the overall aggregated sum across the datasets.

The SUM function computes the sum of actual values for different quarters. We followed that with IF statements to filter and sum the actual values based on specific months. This segments the data to give insights into performance during specific quarters.

00:18:53 Visualizing the data and calculating progress

Once the function is correct, we click OK. This creates the table with all the information we require in the report design pane. We need to move our target, baseline, and cumulative fields into the report. We move the indicator to our rows and measures to columns. This displays the indicators, targets, baselines, and quarterly data.

To achieve the final goal of showing the progress report, we need to calculate the "Progress to Target". We will employ a calculated measure where we divide the cumulative value from the calculated table by the targets. We add a calculation using SUMX. The formula is essentially the ratio between the sum of the cumulative value and the sum of the target values. We use SUMX to iterate through the rows in the table, evaluate the expression, and sum up the result. Once renamed, it appears as the last column for the table.

00:23:55 Q&A session

Is it possible to combine two or more calculated tables into one as a dashboard? If the calculated tables are within the same pivot table report, yes, you can use them and combine them as you will for your visualization or reporting.

Can we write a quick way for coding? If we write too long coding, we might write wrong coding. The length of your formula depends on what you need to achieve. Sometimes we use the pipe operator to improve readability and streamline the formula. Generally, the logic behind your formula determines its length.

Can you please share the database exercise template? We will be sharing the presentation and the video in a couple of days.

I need to make something similar, but in my case, the target can change every quarter. Could you explain that case? If your target changes every quarter, your data model likely has multiple columns for each quarter's target. You could create a calculated table that creates a unified table pulling in the value for each quarter and the target values for each quarter, then run the calculation of the actual versus target based on the quarter-specific field.

Why don't percentages add up to 100% in the final columns? We measured the progress so far in percentage. It wasn't a total at the end. We calculated the ratio by dividing the cumulative by the target for each indicator. It is the average across the indicators and across each quarter, not a sum.

How did you go about learning all that was possible with this feature, especially nesting queries? A good way is to explore and practice. Once you have the logic of what you want to achieve, try to explore that using the syntax. The ActivityInfo team is available to provide support. We are also planning on producing more resources, tutorials, and how-to guides on our website to explore specific formula constructs.

One question about the UNION function. I have created a calculated table selecting columns common from different subforms. It would be helpful to create a new column indicating which form the registrations come from. If you want to create a new column to indicate the source, you can explore the ADDCOLUMNS function. This allows you to add a new column to your calculation.

Can we save the calculated table in a separate form? You can save the calculated table as a report. You can save several calculated tables as several reports. The calculated tables you create are stored within the pivot table report where you created them.