00:00:00 Introduction

We are really excited to be here today on this webinar series for calculated measures. We thought we would do a three-part webinar series to go over this suite of new features that is part of our latest release. We wanted to help you take advantage of the recent enhancements related to ActivityInfo's analysis capabilities. These enhancements include improvements to the way that our pivot tables work, as well as a brand new feature, calculated measures. Altogether, these enhancements open up a whole new world of possibilities to take your analysis to the next level.

There is a lot to cover to fully take advantage of these new features, so we prepared three sessions. In our first session, we will be covering the foundational features that serve as the building blocks for any kind of analysis on ActivityInfo: formulas and pivot tables. We will also introduce how calculated measures can enable us to expand the analysis that we can do. In our second session next week, we will give you the opportunity to master calculated measures by going through some practical real-world exercises in an interactive workshop. Finally, two weeks from now in our third session, we will be hosting an office hour session where you can come and ask us about any challenges that you are encountering as you implement calculated measures for your own projects.

00:02:57 Understanding the concepts

In this section of the webinar, we will take a moment to understand or refresh our understanding of formulas, pivot tables, and calculated measures. Let's dive into these topics to ensure that we are all on the same page. We start with what a formula is. A formula is basically an expression that operates on values in a range of cells. Formulas enable you to perform calculations such as additions, subtractions, multiplications, and division. You can find out averages, calculate percentages, manipulate dates and time values, and do lots more. Formulas allow users to derive new insights or to transform existing data based on predefined conditions.

Think of formulas as a recipe that tells the computer what to do with the data. It takes inputs in the form of numbers, text, or other data, and it applies functions to produce an output. For example, you can use a formula to check if a condition is true or false, or you can combine values from different forms or fields. Similarly, in ActivityInfo, formulas are that set of instructions or expressions used to perform calculations or to manipulate data automatically. By understanding and using formulas, you can save time, automate calculations, and gain valuable insights from your data.

Next is pivot tables. A pivot table is a powerful tool that helps you organize and summarize large amounts of data in a structured and meaningful way. It allows you to quickly analyze and make sense of your data at a glance. Imagine we have a huge form containing records of data with many rows and columns. A pivot table will take this data, reorganize it, and make it easier for you to understand patterns, trends, and relationships. It allows you to group and summarize data based on different categories or variables of interest. In ActivityInfo, we can expand the analytical capabilities of pivot tables with calculated measures, which we do with the help of formulas.

A calculated measure is an advanced custom calculation that you create using a formula or an expression within a data model in pivot tables. When you add a calculated measure, you are introducing a new data point that isn't directly present in the original data set but is derived from the existing data set. This can be achieved through various mathematical operations, aggregations, and logic-based calculations. Calculated measures enable you to create your own custom calculations to fit your specific requirements. For instance, a calculated measure would allow you to combine data from multiple forms into a single measure. It is important to note that calculated measures differ from calculated fields because calculated measures don't have a specific context tied to a form like a field does.

00:08:26 Interactive quiz

We have a speed quiz ready to reinforce the concepts of formulas, pivot tables, and calculated measures. The questions are set based on what I have just explained.

To reinforce what we have learned today: a formula is like a recipe that tells the computer how to handle your data, allowing for calculations and insights. Pivot tables help organize and summarize data, helping you uncover patterns and relationships very quickly. Finally, calculated measures enable advanced custom calculations or deeper data analysis, and they are different from calculated fields because they are not tied to a form context.

00:12:39 Using formulas in ActivityInfo

Now we are going to cover how we actually use formulas in ActivityInfo. There are two main tasks that you can achieve with formulas. First, you can calculate values. This can be done in the data entry part of ActivityInfo by adding calculated field types as part of your form, or in data analysis by adding calculated fields within pivot tables. Second, you can use formulas for defining rules, which is determining a specific condition that needs to be met in order for some action to be taken. Rules are useful in many areas within the app, such as relevance rules, validation, permissions, and locks.

Let's explore how you can make this happen within the app. Looking closer at calculating values, at the most basic level, you can use formulas to do basic arithmetic using standard mathematical symbols. We use symbols to represent the numbers and operations, and we also use symbols to refer to fields in our forms. When it comes to defining rules, the key thing to remember is that you are not just limited to calculating a specific value. You can use formulas to evaluate whether a certain condition is true or false using comparison operators like greater than, less than, or equality tests.

There is a straightforward three-step process for writing a formula in ActivityInfo. The first step is to define your output: what is the final result you need? Step number two is to identify your inputs: think about all the inputs required across the different components of your formula. Finally, step three is combining your inputs: putting everything together in a logical fashion to get the output you defined.

When identifying inputs, there are a couple of different ways to symbolize a field in ActivityInfo. You can use the label of the field, the field code, or the internal ID of the field. We strongly recommend that you assign simple but clear codes to your fields to help you quickly identify which fields you are including in your formulas. It is also important to keep in mind that ActivityInfo's formula language is modeled after Excel's formula language, so the syntax and functions should be fairly familiar.

00:28:25 Formula demonstration

To make this come to life, I will proceed with a quick demo. We are using the simple 3W database template available on our website. This database contains forms commonly used in humanitarian settings where coordination needs to happen across multiple implementing partners. We have a list of projects and cluster-specific 3W forms. I will open the Health Cluster 3W form. The records represent a specific report of the results of a particular activity within the current reporting month.

I might want to analyze how certain demographic groups relate to the entire population of beneficiaries reached. For example, I might be interested in the percentage of children reached out of the total number of beneficiaries. To configure that formula, I am going to add a calculated field. I will give this a label of "Percentage of children." I will open up the formula editor. The first part of our formula will be taking the total of the girls and the boys. I will find the inputs I need: the "Total Girls" field, enter the addition symbol, and add that to the "Total Boys." I will enclose these two fields in parentheses to combine them as the numerator. Then, I will use the division symbol to divide that by the denominator, which is the "Total Beneficiaries Reached." Since we are trying to express a percentage, I will multiply by 100.

00:34:58 Designing a pivot table

A pivot table in ActivityInfo allows you to create reports that connect to your data in real-time. When you are creating a pivot table, you are not altering the original data in your forms. To design a pivot table, there are certain steps you can take. First, add a measure. A measure defines the specific value that you want to analyze and summarize in a pivot table. Second, add dimensions in terms of rows and columns. Dimensions are the categorical variables used to catalog and group data to gain insights. Lastly, you can use a filter to get even more nuanced results.

Pivot tables can be visualized as tables, bar charts, pie charts, or line charts. You can save your pivot tables as a report in your database, meaning anyone with access to that database can view it, or you can save it in "My Reports" for exclusive access. It is important to emphasize that the power of pivot tables in ActivityInfo is in its ability to facilitate dynamic aggregation and summarization. Measures in pivot tables behave differently depending on the input; quantity fields are most commonly used, while text fields generally reveal the count of the text value.

00:38:55 Pivot table demonstration

To design a pivot table, navigate to "My Reports" and select "Add pivot table." You must select the data source, which can be folders or forms within your database. Today we will use the same 3W database template and the WASH Cluster 5W project. Once you have chosen the data sets, it is time to add a measure. I will drag the count of all records into the measures pane. The table begins to take shape.

Now let's add a dimension as a column. We want to find the aggregates of the count of all our records across the states or regions. I will drag and drop the "State/Region" field into the rows. You can see it changes depending on the dimension included. You can also visualize this as a bar chart or pie chart. If we want to filter, say by geographical codes, we can drag the P-code field into the filter pane and select specific geographies. Once your pivot table is ready, you can save the report.

00:45:33 Introduction to calculated measures

We are very excited to introduce our new feature on calculated measures, which allows us to expand our analysis on pivot tables. When you create a calculated measure, you add a new measure to your data model that goes beyond the fields configured in your forms. Calculated measures are not bound by a row context, so you are not limited to simply combining the fields within the record.

You can combine data from different forms into one measure, use different types of aggregations together in the same measure, aggregate data multiple times at different levels, and apply an explicit filter to your measure. To do these different calculations, you will need a new set of functions designed to work with relational data. We now support new aggregation functions like SUMX that enable you to specify the context within which to run the calculation. We also have table manipulation functions that enable you to create specific tables that facilitate the analysis you need, such as joining data together from multiple tables.

To create a calculated measure, note that they are added in pivot tables. You will see a new "Add calculated measures" button in the measures pane. When using the formula editor for calculated measures, you will now see the list of forms available to you on the right-hand pane, allowing you to select which form to include in your analysis.

00:53:21 Calculated measures demonstration

If we want to aggregate data from different forms and calculate the combined total into one measure, we can do that here. In the 3W database, we have forms for WASH activities and Health sector activities. We want to combine aggregates of beneficiaries from both forms into one measure. We will first add the second form (Health sector) to the fields pane.

Next, we click the calculated measure icon. Since we have both forms, we use the SUMX function. We open parentheses and choose the WASH form. Then we start adding the various fields: Total Girls + Total Women + Total Female Elderly. We close the parenthesis. Then we add a plus sign and use SUMX again for the Health cluster form, adding the same quantity records: Total Girls + Total Women + Total Elderly. Once this is done, we click on done, and it appears in the pivot table. You have successfully combined aggregates from two forms, providing valuable insights into the spread and impact of your intervention.