00:00:00 Introduction

Thank you for joining us today for the third installment in our calculated measures webinar series. If you have been following us on this journey for the past three weeks, we started two weeks ago with a foundational session covering the basic features enabling this new tool. Last week, we went through example use cases and walked through practical applications. Hopefully, following that session, you have had an opportunity to practice using calculated measures and are starting to use them for your own projects.

In today's office hour session, we invite you to bring your questions. Perhaps you attempted to use calculated measures for your project and got stuck; we are here to help you along that journey. We will try to answer as many questions as we can today.

We will start with a brief refresher on the concepts around calculated measures and walk through practical tips to keep in mind during implementation. The majority of the time will be used to address common questions we have received prior to the webinar, as well as live questions from the audience.

00:03:20 Gauging familiarity and confidence

Before we dive into the heart of our session, let's take a moment to gauge where we stand. We are curious to know how familiar you are with the concept of calculated measures to inform how we provide support. It is fantastic to see the diversity in experience and confidence levels amongst our audience. Whether you are familiar, confident, or looking to build your understanding, rest assured that we are here to support you every step of the way.

00:07:30 Refresher and professional tips

Calculated measures are a dynamic tool in your data toolkit that allows you to uncover hidden patterns, make informed decisions, and truly understand the stories behind your numbers. You are navigating a sea of data collected from various forms and databases within ActivityInfo. Calculated measures empower you to make sense of this data and extract valuable insights that guide your actions regarding your projects.

Here are some professional tips to consider before embarking on your calculated measures journey. First, consider form selection and inputs. Imagine you are crafting a recipe; you carefully pick the right ingredients. Similarly, start by choosing the right form and ensuring you have all the necessary inputs at the field level so your calculated measure will deliver insightful results.

Second, grasp your data context. ActivityInfo facilitates relational database design, so your data is arranged in a way that connects and relates. Think of your data as puzzle pieces; grasping the context is understanding how these pieces fit together to form a clear picture of your intervention. This insight helps you combine data correctly and unearth hidden impacts.

Third, remember that calculated measures go beyond row and field context. They are like a community that reveals the full picture, giving you a bird's-eye view of patterns and connections you might miss if you focus only on individual details.

Fourth, develop familiarity with formulas. While you don't need to be an expert, knowing common formula phrases and functions supported in ActivityInfo acts as a compass guiding you through the data wilderness. Finally, define your insights and outcomes. Before creating a calculated measure, define the destination of your analysis to ensure you arrive at meaningful results.

00:16:16 Addressing pre-submitted questions

We received several questions beforehand. A recurring question is whether we can refer to existing pivot tables in calculated measures. The short answer is no, but there are workarounds. You can refer directly to each form that contains the data you would like to analyze, ensuring you have the freshest data to work with. We will also be introducing a table function interface that allows you to explore creating tables yourself. Alternatively, you can export the data to a third-party system for further analysis.

Another question is whether calculated measures can be used in forms. While this is not currently possible, it is noted for future consideration. We also received questions about referring to forms in other databases. The answer is yes, but you must explicitly include those in your data source selection.

Regarding referring to fields in reference forms, you cannot do this directly. However, you can work around it by pulling the data into the current form using a calculated field or ensuring you assemble all inputs into your current form.

00:25:17 Practical example: Agricultural assistance project

To provide more inspiration, let's look at a development assistance use case where you have a results framework tracking multiple indicators calculated from other parts of your database. In this example, we are tracking the average yield in farms supported by an agricultural assistance project. We want to track yields over time and compare them against target values.

The target values are pulled from a "Results Framework" form, while the actual average yields are reported in a separate "Annual Farm Survey" form. To connect these two data points, we added a reference field in the survey form pointing to the results framework. This specifies that the records are associated with the "average yield" indicator.

In the pivot table, we included both forms as data sources. We dragged the target values directly from the results framework. for the actual results, we created calculated measures using the AVERAGEX function. The syntax involves specifying the table ID (the Annual Farm Survey) and an expression to filter the results by year using an IF statement. For example, for 2021, we filter records where the year is 2021 and take the average of the "Total Yield" field. We repeated this for 2022.

Finally, to calculate the year-on-year percentage change, we created another measure. The numerator is the difference between the 2022 and 2021 values, and the denominator is the 2021 value. This demonstrates how you can combine different aggregations to derive complex calculations needed for analysis.

00:39:54 Live Q&A session

Is it possible to make goals as calculated fields, and is it necessary for the form to be a reference form?

If you want to ensure you are connecting data correctly to specific indicators where you have target information stored, it is advisable to create a reference field pointing to the form containing that indicator information. In our example, we created a reference field pointing to the Results Framework form. This allows the pivot table to draw a connection between the survey values and the targets; otherwise, the system wouldn't know those results were associated with the specific indicator.

Can you show how to build the formulas step-by-step?

To calculate the average yield, we use the AVERAGEX function because we are calculating across a table, not just a single row. The first argument is the table containing the data (Annual Farm Survey). The second argument is the expression. We use an IF statement to filter: if the Year field equals 2021, we use the value from the Total Yield field. This provides the average for that specific year.

Can we have a calculated measure that calculates the percentage change between the current month and the previous month?

Yes, you can use calculated measures incorporating dynamic date functions. For example, you can use the TODAY function to establish the current date and filter your records to include dates within a specific range (e.g., 30 days from today) to isolate the previous month's data. You would define the base month and the previous month in your filter to perform the calculation.

Is it possible to build a calculated measure over another calculated measure, specifically for disaggregated data?

This use case, where you need to create reusable measures for subsequent analysis (like aggregating disaggregated data by age and gender into a total reach), is relevant for upcoming functionality regarding "Calculated Tables." This feature will allow you to save calculated tables containing specific measures, which can then be used as inputs for further calculations, such as a total sum.

00:56:24 Conclusion

That concludes the questions for today. Thank you to everyone who sent in questions; it helps us understand exactly how you are using these features. We appreciate your participation in this series and hope you are now more confident in applying calculated measures to your projects. Stay tuned for more updates, including the upcoming calculated tables functionality.