Calculate Running Sum
Analytical Need
There are cases where you wish to know for a certain point in time, how much you have accumulated up until now.
This is called Running Sum.
Although it is possible to use this in the dashboard, we may need to have the running sum on each and every record and not to have it broken down according to the categories in the widget.
Modeling Challenge
Currently, there are no previous, lag and other record referencing functions that enable us to see what is there is the previous record. So we'll need a way to figure out how do we get that information despite that.
Solution
We will perform a join of the table to itself (self join). This way we will be able to match records based on what came before, according to their date.
SQL:
select a.date, sum(b.sales) as cumulative_sales
from sales_table a join sales_table b
on a.date >= b.date
group by a.dateAs you have noticed, table a & b are the same. We just use all the dates that came before and sum up the sales for that.
If you wish to have the running sum based on something more than just date, then add it to the query in the select, in the join clause and in the group by. like so :
select a.date, a.ProjectId, sum(b.sales) as cumulative_sales
from sales_table a join sales_table b
on a.date >= b.date and a.ProjectId= b.ProjectId
group by a.date, a.ProjectIdNow the field cummulative_sales will appear for each date & ProjectId.
Updated 03-02-2023
