Compare time periods in Visual SQL

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 one month’s data to the previous month’s data as a basic starting point. For this example, we’ll use the Dundersign Demo Data data source to compare this month’s invoice amounts to last month’s invoice amounts.

1. Create a month-over-month (MoM) table by opening the Invoice table and adding the Amount and Created Date columns to the Columns section. Change the time bucket for Payment Date to Month.

   Click Run Query.

2. Add a Formula 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 Total sum of Amount for the column and type 1 for the row offset.

   Click Save.

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

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

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 invoice amount 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.

   Click Save.

   

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

   "Total sum of Amount"-"Previous Month"
   

   For clarity, we also renamed this new Custom column to MoM Amount 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.

   

4. Now we can choose the Single Value chart type.

   

5. This now displays the difference in invoice amount between the current month and the previous month and will update each month. Click the Gear to open the chart settings and name the chart something like MoM Difference in Invoice Amount and enter $ for the Pre text to include units on your number.