cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

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.date

As 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.ProjectId

Now the field cummulative_sales will appear for each date & ProjectId.

Version history
Last update:
‎10-17-2021 05:41 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request