Compare time periods

Chartio gives users the ability to easily compare one period of time to a relative previous period of time, for example, week-over-week, month-over-month, year-over-year, etc.

There are a variety of ways to visualize this through Chartio, but begin by creating a table that compares this month’s revenue to last month’s revenue as a basic starting point. In this example, we will be using the Chartio Demo data source.

In the Visual SQL interface

  1. For this example, we’ll use the SaaS Company Demo Data data source. Create a month-over-month (MoM) table by opening the Payments table and adding the Amount and Payment Date columns to the Columns section. Change the time bucket for Payment Date to Month.

    Add the Amount and Payment Date columns from the Payments table

  2. Add a Calculated Column, selecting Lag as the formula type; this copies the values from one column into a new column but shifts the rows down based on the row offset number. For this example, select Amount for the column and type 1 for the row offset.

    Add Calculated Column, choose Lag, select Amount column, and enter 1 for Row Offset

  3. Rename the Calculated Column to “Previous Month”—or something else—to indicate it’s the amount of revenue from last month.

    This table now has two columns that compare the current month’s revenue to the previous month’s.

    Rename Lag column to Previous Month

There are many ways to visualize this data in Chartio, but in this example, we’ll choose a Single Value chart to show us the difference between this month’s revenue and last month’s.

  1. Add a Limit Rows Action to filter on just a single row of data. We’ll focus on the second row since it has values in both the current month and previous month: limit the rows to 1 and start with row number 2.

    Add a Limit Rows Action to show one row starting at row number two

  2. To get the difference in monthly amounts, add another Calculated Column but select Custom Formula then use the following formula:

    "Amount"-"Previous Month"
    

    For clarity, we also renamed this new Custom column to MoM Revenue Difference.

    Use a Custom Forumla to get the revenue difference

  3. To use the Single Value chart, we need to reduce the number of columns to one, so we’ll hide the first three columns.

    Hide the first three columns in the Result Table

  4. Now we can choose the Single Value chart type and edit the chart settings.

    Select the Single Value chart type

  5. This now displays the difference in revenue between the current month and the previous month and will update each month. Click the Paintbrush to open the chart settings and name the chart something like “MoM Difference in Revenue” and enter $ for the Pre text to include units on your number.

    Add a chart title and $ pre text


In the Data Explorer interface

  1. Create a month-over-month (MoM) table by opening the Payments table and dragging Amount into the Measures field and Payment Date into the Dimensions field. Change the time bucket to month.

    Create a MoM table

  2. Scroll down to the Pipeline and click +Add Transformation and choose Add Column. Name the column Previous Month (or something else to indicate that it is the amount of revenue from last month). Select Lag as the Formula Type; this copies the values from one column into a new column but shifts the rows down based on the Row Offset number (type in 1 for this value).

    Sample chart

    This table now has two columns that compare the current month’s revenue to the previous month’s.

There are many ways to visualize this data in Chartio, but in this example we will be choosing a Single Value KPI that will show us the difference in this month’s revenue from last month’s.

  1. Add a Limit Rows step in the Pipeline to filter on just a single row of data.

    Add a Limit Rows step in the Pipeline

  2. Add another Pipeline step; this time, choose Combine Columns.

    Add a Combine Columns step in the Pipeline

  3. In the Pipeline, add a Hide Columns step and choose the Month of Payment Date column from the dropdown of choices to hide.

    Add a Hide Columns step in the Pipeline

Single value chart

This now displays the difference in revenue between the current month and the previous month and will update each month. Name the chart something like “MoM Difference in Revenue.” Click Settings and under Pre-text, type a $ to include units on your number.

Add units (a $)