This article explains how to calculate a cumulative sum with partitioning in Saved Search Formula.
The provided formula demonstrates how to achieve this using the SUM function along with the OVER clause. By partitioning the data based on specific columns, such as the month of a date and a sales representative, the cumulative sum can be calculated within each partition.
The formula also includes an ORDER BY clause to determine the order of rows within each partition, and a ROWS BETWEEN clause to define the range of rows included in the cumulative sum calculation. This comprehensive guide will help you understand and implement cumulative sum calculations with partitioning in your SQL queries.
sum/* comment */({amount})
OVER ( PARTITION BY EXTRACT(MONTH FROM {trandate}), {salesrep}
ORDER BY {trandate} ASC, {internalid} ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)This formula calculates the cumulative sum of the {amount} column, partitioned by the month of the {trandate} column and the {salesrep} column. The results are ordered by the {trandate} column in ascending order, and by the {internalid} column in ascending order for ties. Finally, the calculation is performed over a sliding window that includes all rows from the start of the partition up to and including the current row. The /* comment */ is a comment that can be used to add explanatory notes to the formula.
Result:
