Documentation Visual SQL (beta) FAQs

Calculate a running total in Visual SQL

A running total is a common metric to gain insight into how an amount has changed over time. This process can be easily accomplished in Chartio by using the Visual SQL Pipeline to transform your data.

First, open your existing chart or create a new chart if you do not have an existing chart to work from. At a minimum, you’ll need to include the aggregated column for which you’d like to keep a running total and at least one categorical column that will be used to group the running total. For your dimension, we recommend using a date with a time bucket of your choice to best visualize the data you’re interested in seeing, such as month or year.

In this example, we’ll use our SaaS Company Demo Data data source. From the Payments table, we use Amount for the aggregated column, and “Payment Date” with a Month time bucket for our grouped column. Once the columns have been added to the query, click Run Query to populate the chart.

Total sum of Payment Amounts for each Payment Date Month

To calculate the running total of payment amounts, use either an Edit Column or Calculated Column (if you’d like to keep the original data as part of the chart) Action. Here, we’ll add a Calculated Column.

For the formula type, select running total then select the column you’d like to be used as the input column for the running total. In this example, we’d select Amount. Click Save once completed.

To get a clearer view of how the total changes over time, change the chart type to a Line chart.

Final chart with running total

Running total by group

You can also create a running total for groups using SQLite Window Functions.

For example, we’d now like to see the amount we’ve made over time but grouped by plan type. To do this, we’ll need to use a Custom formula in our Calculated Column or Edit Column Actions.

You’ll want to use the following statement, which calculates the sum of Column1 for the groups in Column2 following the order of rows in Column3.

sum("Column1") over (partition by "Column2" order by "Column3")

To add to our first example, we also pulled in the Plan Title column from the Plans table. We used the following statement to get the running total of the “Amount” for each type of plan “Title”, ordered by the Month of “Payment Date”.

sum("Amount") over (partition by "Title" order by "Payment Date")

Running Total by Plan type

We can now see the running total in our table below. As we’ve added a PARTITION BY clause, the running total starts over for every new, unique value in the “Title” column. This same formula can be used in a number of ways such as calculating the running total by month.

To better show this, we narrowed the timeframe to last 3 months of payments in the final chart:

Final Table chart - Running total for Amount by plan type