Documentation

Running Total

 

 

You might want to see your revenue adding up monthly in the course of the year to compare your sales to the year’s target. In datapine, you can use the Accumulate over time function for Drag & Drop charts to calculate the running total of any of your numbers.

 

A normal chart compared to a running total chart

 

 

 

Apply the Accumulate over time function

 

 

  1. Create your chart by adding the fields to X- & Y-Axis. Make sure you add a date field to the Dimensions to be able to accumulate over time.
  2. Click on the field in Y-Axis.
  3. Open the drop down ‘More Options’
  4. Then tick the checkbox Accumulate values over time below.

How to enable running total

 

 

Note: In case you added more than one field to Measures, you will need to select the Running Totals function for each of these fields individually.

 

 

Add accumulation to your SQL query

 

 

In datapine, you cannot use variables to calculate running totals over time. However, by doing a self-join on your query you can still sum up your metric month-by-month over a defined period. If you’ve not connected your own data set yet, you can run these sample queries using our demo database. Please note, these queries use MySQL syntax.   The simplest and fastest solution is to add a sub select with a condition on the date.

 

Select
date_format(s.Date, '%Y-%m') as month,
count(s.SalesID) as "# Sales this month",
(select count(s2.SalesID) from `Sales` s2 where date_format(s2.Date, '%Y-%m') <= month) as "Running Total # Sales"
from `Sales` s
group by 1

 

Another more complex but flexible option is to use two individual subqueries. The main query lays on two identical subqueries t and t2, joined on t2.month <= t.month, so that the sum of t2. sales_no will be the sum of all previous months.

 

Select
t.month,
t.sales_no "# Sales this month",
sum(t2.sales_no) "Running Total # Sales"
from
(select
date_format(Date, '%Y-%m') as month,
count(SalesID) sales_no
from `Sales`
group by 1)t 
left outer join

(select
date_format(Date, '%Y-%m') as month,
count(SalesID) sales_no
from `Sales`
group by 1)t2 
on t2.month <= t.month
group by 1,2