Article

Trend analysis: measuring rates of change

How to get the rate of change or percentage change across an entire dataset at once.

Introduction

In Trend analysis: comparing time periods, we compared a revenue metric across two time periods (for example, month over month) to see if the revenue was growing, shrinking, or plateauing over time.

Now, what if you want to calculate a MoM trend over 24 months of data? The good news is that you don’t have to repeat the time period comparisons 23 times. You can calculate the rate of change across all of the time periods in a dataset at once!

If you’re a spreadsheet aficionado, you might be used to calculating rates of change using a row or column offset. In this tutorial, we’ll calculate rates of change using self-joins in the query builder.

We’ll create the following trend reports:

Dollars per month:

<em>Fig. 1</em>. For example, if we want to say that the revenue increased from $7,701.40 to $9,236.80 between Jan 2021 and Feb 2021.
Fig. 1. For example, if we want to say that the revenue increased from $7,701.40 to $9,236.80 between Jan 2021 and Feb 2021.

Dollar change from last month to this month:

<em>Fig. 2</em>. If we want to say that the revenue increased by $1535.40 from Jan 2021 to Feb 2021.
Fig. 2. If we want to say that the revenue increased by $1535.40 from Jan 2021 to Feb 2021.

Percentage change from last month to this month:

<em>Fig. 3</em>. If we want to say that the revenue increased by 19.94% from Jan 2021 to Feb 2021.
Fig. 3. If we want to say that the revenue increased by 19.94% from Jan 2021 to Feb 2021.

We’ll also show you how to set up a chart tooltip like this:

<em>Fig. 4</em>. Display a neat monthly summary in the tooltip.
Fig. 4. Display a neat monthly summary in the tooltip.

Creating a monthly trend report

When you’re doing time period comparisons month over month using the sumif function, you have to “hard code” or specify the exact months you want, like this:

SumIf([Payment], between([Date Received], "2022-10-01", "2022-10-31"))

But hard coding gets tedious real quick—you’d have to add a new expression every month, just to keep up! To get the monthly trend over all of the months in the Invoices table at once (even as the table gets updated with fresh data), we want to set up a table like this:

Date Received: Month Sum of Payment Previous Month Sum of Payment (Previous Month) MoM Dollar Change
Jan 2021 7,701.4 Dec 2020 6,197.4 1504
Feb 2021 9,236.8 Jan 2021 7,701.4 1535.4
Mar 2022 11,319.2 Feb 2021 11,319.2 2082.4

Here’s how we’ll create the table and display our findings:

  1. Calculate the total payments per month to get “Sum of Payment”.
  2. Add a column to get “Previous Month”.
  3. Reshape the data with a self-join to get “Sum of Payment (Previous Month)”.
  4. Calculate “MoM Dollar Change”.
  5. Visualize and interpret our results as:

Summing payments by month

First, we’ll sum the payments per month in the Invoices table to get a results table that looks like this:

Date Received: Month Sum of Payment
Jan 2021 7701.4
Feb 2021 9326.8
Mar 2021 11319.2
  1. Click + New > Model > Use the notebook editor.
  2. Click Raw Data > Sample Database > Invoices.
  3. Click Pick the metric you want to see”.
  4. Select “Sum of…” > “Payment”.
  5. Click Pick a column to group by.
  6. Select “Date Received: by month”.

You can click the play button to preview the results (but don’t click save just yet):

<em>Fig. 5</em>. Calculating the total payment dollars per month.
Fig. 5. Calculating the total payment dollars per month.

Getting the previous month

Next, we’ll create a custom column called Previous Month to store a one month offset from Date Received: Month.

Date Received: Month Sum of Payment Previous Month
Jan 2021 7701.4 Dec 2020
Feb 2021 9326.8 Jan 2021
Mar 2021 11319.2 Feb 2021
  1. Click Custom column.
  2. Enter the following datetimeSubtract expression:
      datetimeSubtract([Date Received], 1, "month")
    
  3. Name the custom column “Previous Month”.
  4. Optional: go to Metadata in the top nav to format the date in “Previous Month”.
  5. Save the model and name it “Monthly Payments ($)”.
<em>Fig. 6</em>. Creating a custom expression to get the previous month.
Fig. 6. Creating a custom expression to get the previous month.

Creating a self-join on the previous month

Now, we’re going to reshape our data so that each row looks something like this:

Date Received: Month Sum of Payment Previous Month Sum of Payment (Previous Month)
Jan 2021 7,701.4 Dec 2020 6,197.4
Feb 2021 9,236.8 Jan 2021 7,701.4
Mar 2022 11,319.2 Feb 2021 11,319.2

We’re going to do this “reshaping” by joining Monthly Payments ($) with itself (also known as a self-join).

  1. Click + New > Question.
  2. Click Models and select the “Monthly Payments ($)” model.
  3. Click the Join data button (venn diagram icon) to start a left join.
  4. Click the back arrow (<) in the popup menu.
  5. In the popup menu’s search bar, type Monthly Payments ($) and select the model.
  6. For the join condition, select Previous Month: Month = Date Received: Month (make sure “Previous Month” is on the left).
  7. Click the dropdown arrow beside Monthly Payments ($) (inside the Join data box).
  8. Uncheck “Date Received” and “Previous Month”.
  9. Click Visualize.

Now, each row of our model contains all of the information for a month-over-month calculation.

<em>Fig. 7</em>. Click the arrow on the right to preview the results of the self-join.
Fig. 7. Click the arrow on the right to preview the results of the self-join.

To rename the second “Sum of Payment” column:

  1. Click the Settings button (beside Visualization).
  2. Click the three-dot menu beside the second “Sum of Payment”.
  3. Change the Column title to “Sum of Payment (Previous Month)”.
  4. Click Done.

Calculating the month-over-month difference

Finally we’ll add a column with that summarizes the month-over-month difference.

Date Received: Month Sum of Payment Previous Month Sum of Payment (Previous Month) MoM Dollar Change
Jan 2021 7,701.4 Dec 2020 6,197.4 1504
Feb 2021 9,236.8 Jan 2021 7,701.4 1535.4
Mar 2022 11,319.2 Feb 2021 11,319.2 2082.4
  1. Open the query builder and click Summarize.
  2. Click Custom column icon (square grid with a plus symbol).
  3. Enter the expression:
    [Sum of Payment] - [Question ID → Sum of Payment]
    
  4. Name the column “MoM Dollar Change”.

Note that “Question ID → Sum of Payment” is the previous month’s “Sum of Payment”. You can type “Sum of Payment” to select the right column name (you’ll probably see a different question ID).

<em>Fig. 8</em>. Getting the month-over-month difference in every row.
Fig. 8. Getting the month-over-month difference in every row.

Visualizing a monthly trend as a line chart

To look at the total payments each month, we can plot “Sum of Payment” by “Date Received: Month”.

If we do this directly from a question on the Invoices table, we’ll be able to say that “the revenue increased from $7,701.40 to $9,236.80 between Jan 2021 and Feb 2021”.

But since our model has a column with the MoM change per month, it’ll be easier to say that “the revenue increased by $1535.40 from Jan 2021 to Feb 2021”.

  1. Click Visualization button.
  2. Select the Line chart.
  3. For the X-axis, select “Date Received”.
  4. For the Y-axis, select the first “Sum of Payment”.

Optional: add points to the line chart:

  1. Click the three-dot menu beside “Sum of Payment”.
  2. From the popup menu, select Style.
  3. From Show dots on lines, click On.

To clean up the chart tooltip so that it doesn’t show the previous month’s “Sum of Payment”:

  1. Open the query builder.
  2. From the Join data box, click the dropdown arrow beside Monthly Payments ($).
  3. Uncheck “Sum of Payment”.
<em>Fig. 9</em>. Line chart of payments per month.
Fig. 9. Line chart of payments per month.

This kind of trend chart can show us how the absolute value of revenue changes over time. If we were to analyze this (fake) data, we can see that the trend in payments:

  • Grows rapidly month over month (the upward trend is nearly vertical).
  • Seems affected by seasonality (the trend resets and repeats every 12 months).
  • Behaves a bit suspiciously (the trend line never goes down within each 12 month cycle).

Visualizing rate of change in dollars

Okay, now that we know our revenue is growing, we might want to know when this growth is happening the fastest. To get an intuitive sense of the rate of change, we’ll plot the MoM change (in dollars) over time:

  1. Click Settings button (beside Visualization).
  2. Change the Y-axis to “MoM Change”.
<em>Fig. 10</em>. Plotting the rate of change in dollars.
Fig. 10. Plotting the rate of change in dollars.

Compared to the monthly trend chart, the rate of change chart shows us how fast the revenue is changing over time:

  • The upward spikes have slight curved pattern, so growth might have been accelerating over the course of a 12 month cycle.
  • The seasonal effect causes revenue to decelerate very fast (sharp downward spike) between each June and July.

Visualizing rate of change as a percentage

In the dollar rate of change chart, we have to use the visual slope of the line to estimate the speed of revenue growth.

We can also look at the quantitative rate of change by calculating the percentage change from month to month. So, instead of saying that “the revenue increased by $9326.80 from Jan 2021 to Feb 2021”, we can say that “the revenue increased by 19.94% from Jan 2021 to Feb 2021”.

To add MoM percentage to our current question:

  1. Open the query builder.
  2. From the Custom column section, click the plus button (+) to add another column.
  3. Enter the formula (and remember to replace the Question ID):
    [MoM Dollar Change] / [Question ID → Sum of Payment] * 100
    
  4. Name the custom column “MoM Percent Change”.
  5. Click Visualize.
  6. Click Settings button (beside Visualization).
  7. Change the Y-axis to “MoM Percent Change”.
<em>Fig. 11</em>. Plotting the rate of change as a percentage.
Fig. 11. Plotting the rate of change as a percentage.

Now, if we compare this chart to the rate of change in dollars, we can get more specific details:

  • The relative change is greatest from Oct 2020 to Dec 2020 (likely since the payments per month are small).
  • The growth rate fluctuates over the course of each 12 month period (so the revenue isn’t growing by 2%, 4%, 8%, etc).
  • The annual decline in revenue corresponds to the near 100% drop each July.

Examples of more trend reports

To create:

Further reading

Thanks for your feedback!

Get articles like this one in your inbox every month