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 Atlassian Analytics by using Visual SQL steps 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 categorical column, 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 Atlassian Data Lake data source. From the Jira Issue table, we use Issue Id for the aggregated column, and Created At 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.

To calculate the running total of amounts, use either an “Apply formula” or “Formula column” (if you’d like to keep the original data as part of the chart) step. Here, we’ll add a Formula 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 Issues. Click Save once completed.

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

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 “Formula column” or “Apply formula” steps.

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 Project Type column from the Jira Project table. We used the following statement to get the running total of the “Issues” for each type of Project type”, ordered by the “Month of Created At”.

SUM("Issues") OVER (PARTITION BY "Project Type" ORDER BY "Month of Created At")

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 “Project Type” 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 issues in the final chart: