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.

 

how to accumulate over time

 

Apply the Accumulate over time function

 

  1. Create your chart by adding the fields to Measures & Dimensions. Make sure you add a date field to the Dimensions to be able to accumulate over time.
  2. Click on the field in Measures.
  3. In the Chart Property & Field Editor on the right, define the preferred aggregation type for this field.
  4. Then tick the checkbox Accumulate over time belowand click apply.

how to apply acculate over time function in datapine

 

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