Hi all,
I have a table F_SALES
Region SalesRep Month Sales
North John 2021-01 10
North Jack 2021-01 12
North John 2021-02 15
North Jack 2021-03 10
North Jones 2021-03 10
North John 2021-03 10
South Jack 2021-01 20
South Jack 2021-03 25
....
I need to calculate a cumulative Sum over the months.
I tried using the PARTITION BY clause, as folllows:
Select Region,
Month,
` Sum(Sales) over (Partition by Region Month Order by Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
as Sales_MTD
From F_SALES
The result is:
Region Month Sales
North 2021-01 22
North 2021-02 15
North 2021-03 30
South 2021-01 20
South 2021-03 25
My problem is: There is obviously no cumulated value for South 2021-02, because so is no record in the original table.
Any idea about how to "fill" the gaps in the cumulative sum ?
Or any workaround to suggest ?
TIA !
Mehdi