Introducing the all-new TIBCO Community site!

For current users, please click "Sign In" to reset your password and access the enhanced features. If you're a first-time visitor, we extend a warm welcome—click "Sign Up" to become a part of the TIBCO Community!

If you're seeking alternative community sites, explore ibi, Jaspersoft, and Spotfire.

Jump to content

How to sum previous 12 rows for each data entry


Noah Brous

Recommended Posts

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

Link to comment
Share on other sites

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]))

Link to comment
Share on other sites

×
×
  • Create New...