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

intapiuser

Posted 3 years ago·Last reply 1 year ago

1 comment

LBYbrian

·1 year ago

I am getting the following error when using this self join format. Error is

Exception in DirectCommand.ExecuteReader(): DirectCommand.ExecuteReader failed: Query number 0: MALException:algebra.thetajoin_prll:GDK reported error.

Is there a better sql to use in Sisense for cumulative sales?