Rollingsum syntax

[johnsa21]

Hi - im having trouble with the rollingsum function - I would like to exclude the current record and only look at the previous records.

The syntax I'm using is rollingsum(field,-1,0) - which excludes all next records but includes all previous and the current - I would like to exclude the current

[TrifactaUsers]

Hi,

If I understood correctly, I tried as below and let me know if that matches your requirement.

[TrifactaUsers]

attached is the input file I used.

[Trifacta_Alumni]

@Sridar Krishnamoorthy? your solution will only remove the first item from each group. As I understood the question @Andrew Johnson? is looking to exclude each record from the calculation of it's rolling sum. The easiest way to do this would be to subtract the record from the ROLLINGSUM function, e.g.:

ROLLINGSUM(field,-1,0)-field

Hope this helps!

[TrifactaUsers]

@Alon Bartur? Nope, If you see the previous working example attachment,

1. the first line will have the same value for salary and newrollingsum().
2. second line will have first line's salary and
3. third line will have first + second salary group by id.

Moreover ROLLINGSUM(field,-1,0)-field will result first line value = 0 for new group.

[Trifacta_Alumni]

Got it, I still think that the first line value should be 0 if you're looking to exclude the current value but if that's not the case then your solution would work

[johnsa21]

Hi Both - the example Sridar shared seems to work - thanks!

One more question - it appears you created the columns without a window - when I try to create the columns with a "new formula" it requires I use a window function - how do you do that?

[TrifactaUsers · Accepted solution]

Pls find attached recipe and let me know if it doesn't work!