Quickbase Analytics: Using Moving Averages to Identify Trends
Quickbase Analytics:
Using Moving Averages to Identify Trends
This article is the second in the Quickbase Analytics series. These posts outline different approaches builders can use to improve the analytical insights they gain from their Quickbase apps. This post builds on top of the first entry, which outlines the benefits of setting up a Months table. These posts are geared towards experienced builders as some more advanced concepts are used.
Use the Quickbase Analytics Example App to follow along
In the first post of this series, we made the case for setting up a Months table to improve the month-to-month insights users can get from their apps. This is great for many reasons, however there are times when a 30-day timeframe is too short to identify stable trends. We need to make sure we aren’t overreacting to a single especially strong or weak month, but rather look at the bigger picture. We can achieve this by utilizing a moving average.
In this post we’ll create a 3-month moving average. Meaning, instead of tracking each month individually, we’ll group each month with the two months immediately before it and calculate the average (sum of the months divided by the # of months). For example, if one month we bring in $15,000 in revenue, and the two months before we bring in $10,000 and $5,000, our 3-month average is $10,000 ($15,000 + $10,000 + $5,000 / 3). In the next month, if we bring in $25,000 our 3-month average would jump to $16,667 ($25,000 + $15,000 + $10,000 / 3).
To get a better sense of the usefulness of a moving average, let’s compare two graphs: one displaying monthly revenue, and one displaying a 3-month revenue moving average:
The first thing that jumps out in the Monthly Revenue graph is that April was by far the lowest month displayed. However, March was our best month, so while we still see a dip for April in the 3-month moving average graph the drop is less severe. Additionally, we followed April with a solid May and June. As a result on the 3-month moving average we see a fairly smoothed out line for April-May-June.
Another noticeable difference is July – September. On the Monthly Revenue graph, we see 3 similar months, ending with a slight uptick September. We might think this is good! But what actually happened was we had our worst 3 months after April in a row. Once our solid month of June is no longer part of the calculation in September, we see our lowest point in the graph.
Now that we understand the usefulness of a moving average, lets learn how to set it up! In this post we’ll use the same data/example as the previous post on setting up a Months table to create the 3-month moving average used in the graph above.
Setting up Moving Averages:
Our first step to implementing moving averages is to update the Months table so that the previous months' Month Start value is captured on each record. Since we are creating a 3-month average, we need each record to include the previous two month's Month Start. For this I created the fields Month -1 and Month -2. You can download a csv file with months from 2017 – 2023 and these columns from the example app.
Once we’ve captured the previous months, we need to create a recursive relationship. That is, we need to create a relationship with the Months table to itself. This can be a confusing concept, but essentially this lets us associate two records from the same table to each other. In our case, we’re able to associate a given month with it's previous month.
For the reference field, we will select Month -1:
Don’t worry about lookups, just go ahead and create the relationship. Then, we are going to create another recursive relationship but now select Month -2 as the reference field. Afterwards, our relationships page on the Months table should look like this:
Now that we’ve successfully linked each month to its previous two months, we can aggregate the 3-month totals. Click on the either one of our Months < Months relationships, click Add Lookup Fields, then select Total Revenue. We should then see the lookup listed like this:
Repeat this for the other Months < Months relationship so that there is a Lookup field for Month -1 – Total Revenue and Month -2 – Total Revenue. To visualize what we just did, take a look at the table report here:
Now we can find the 3-month average with a simple formula field. Let’s create a field named 3-Month Average with the formula:
We can see the formula results on our table report:
Now we can create the line graph shown at the top of the post, or any other visualizations we’d like!
If you want to try implementing moving averages your app, check out the Quickbase Analytics Example App. There is a .csv ready to be downloaded containing everything you need to set up the Months table: Quickbase Analytics Example App
