Jump to content

rolling sum over a time period (24hrs.)

Justin Schneider

Recommended Posts

Hey guys I am looking for some help summing up values over a 24 hr. time period. I've attached a excel snanpshot to help explain what I am looking for, but I can't seem to figure it out in spotfire.


I have tried last periods, but that only helps if I have data coming in on an hourly basis. Sometimes data only comes in every 2-3 hours so I cant say last 24 periods. I need to be able to specify an actual time frame.


Thanks for your help!

Link to comment
Share on other sites

Assuming that you do indeed want a rolling 24-hour average...


From your screenshot, it looks like your events do not happen regularly in time.

If you had a row per hour, you could simply sum over the last 24 rows.

So if you could get yourself a table that does have one row per hour, where that row contains the accumulated values over that hour, then the problem is reverted to just doing that.

I can think of a way of doing this, there might be easier ways and I did not have a sample dataset to verify it. Anyway hope it helps.

To do this:

in your original table:


1 - calculate the date-time of your event binned by hour as


call it [eventHour]


2 - calculate a row id (used later) as


call it [ROW]

Note: I am assuming your data is naturally ordered by the date-time.


3 - calculate the sum of your desired column over each hour, as

Sum([pumped_Weight Simplified]) over (Intersect([eventHour],[any_other_grouping_column]))

where the [any_other_grouping_column] is a placeholder for any column you want to use to group/reset your count.

call it [sumOverHour]


Now add a new table: click on + then 'Other' then choose from 'Linked copy to data table in analysis' which mean you are adding a copy of your original table.

In the data canvas, click on your new table and Add transformations > Filter rows

set your filter as

[ROW]=First([ROW]) over (Intersect([eventHour],[any_other_grouping_column]))

now your new table contain one row per hour, and you have already added up your [pumped_Weight Simplified] into [sumOverHour]

In the new table, you then calculate [Daily Total Throughput] as

Sum([sumOverHour]) over (Intersect([any_other_grouping_column],LastPeriods(24,[ROW])))

If you don't have any additional grouping column, then you can remove all the Intersect(..)

Link to comment
Share on other sites

Use a combination of the DateDiff('day', ) and DateTimeNow() function and wrap it into Floor() or Ceiling() function. Something like:

Floor(DateDiff('day', DateTimeNow(), [End_Time]))This should result in 0,1,2,3,4...

Use this in the Intersect() part of your calculated column

If you want to refresh the calculation at any time then it is better to store the DateTimeNow() into a document property. You can do this with a simple IronPython script or TERR data function. Add a button to your analysis to recalcualte this Doc Prop any time so that your calculated column or expression will refresh.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...