Compare time periods in Data Explorer

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 dragging Amount into the Measures field and Created Date into the Dimensions field. Keep Amount’s Aggregation as Total sum of and change Payment Date’s Time Bucket to Month of.

   

2. Click +Add Transformation and select Add Column. Name the column Previous Month (or something else to indicate that it’s the invoice amount 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, which will be 1 for this example. Select Total sum of Amount for the Input Column.

   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 step in the Pipeline 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. Enter 1 for both the Limit and the Offset.

   

2. Add a Combine Columns step to get the difference between this month’s and the previous month’s values. Select Hide Combined Columns to hide the columns used in the subtraction.

   

3. Add a Hide Columns step to hide the Month of Created Date column.

   

4. Now 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. Name the chart something like MoM Difference in Invoice Amount. Click Settings and type $ in the Pre-text field to include units on your number.