Documentation Visual SQL (beta) FAQs

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

  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