Search code examples
spotfire

Using OVER function in Spotfire to produce cumulative sum in a data table

I have a beginner question about OVER statements and cumulative summing:

Imagine that I have a table that has years in the first column, and sales in the second column, e.g.:

Year  Sales
2001  10
2002  20
2003  30
...

What I want to do is add the sales from one year to the next, and have these running down a column in Spotfire. With the case of the first year, I just want to show sales for that year, e.g. 

Year  Sales Year sales + Year -1 sales
2001  10    10
2002  20    30
2003  30    50
...

I tried the following OVER statement:

If(
  Count([Year]) over (Previous([Year]))=0, 
  [Sales], 
  [Sales] + Sum([Sales]) Over (Previous([Year])))

... but I get the weird result of:

Year  Sales  Year sales + Year -1 sales
2001  10     10
2002  20     <seemingly null>
2003  30     50
...

Any thoughts on what I'm doing wrong? In short, I'm trying to work out how to have a different result against the first node (I think this is the correct term!! :) )

I played around using the THEN statement as described in this SO page, but did not seem to help.

Solution

  • This formula should help you get the desired result.

    Sum([Sales]) over (LastPeriods(2,[Year]))

    YearSales