How to sum previous 12 rows for each data entry

[Noah Brous]

For example, there is a data set with 100 rows and 5 columns. I would like to create a calculated column which does the following:

For each row the calculated column will sum the previous 12 entries in column 5.

e.g. Row 32 of the calculated column will be the sum of row 21 through 32 in column 5.

Row 33 of the calculated column will be the sum of row 22 through 33in column 5.

etc.

Currently I have a data set which is sorted by calendar week and there are multiple entries (rows)per calendar week. I would like to sum the previous 12 calendar weeks worth of data per row in the calculated column. I can simplify the data to have a single row per week (the example I listed above) but I would rather use a calculated column that looks for the last 12 calendar week entries and sums them. I feel being able to insert a range which changes with the calculated column rowinto the "OVER" function will fix my problem (sum([data]) over (1

[Gaia Paolini]

See this answer:https://community.tibco.com/questions/rolling-average-calculated-column

In your case I would first create a column called e.g. [index] containing RowId() (as OVER does not work with expressions) or use any existing column that gives you an order - then do

Sum([YourColumn]) OVER (LastPeriods(12,[index]))