rolling sum over a time period (24hrs.)

[Justin Schneider]

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!

[Gaia Paolini]

Can you clarify In your screenshot, since you are asking for the running total over a calendar day, it then resets at the start of the new day.

 

Are you meaning to calculate a running total over the previous 24 hours, regardless of where you are in the date

[Gaia Paolini]

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

BinByDateTime([END_TIME],'year.dy.hh',3)

call it [eventHour]

 

2 - calculate a row id (used later) as

rowid()

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(..)

[Fabian Duerr]

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.

[Justin Schneider]

Yes that is correct. I am looking for a running 24hr sum of pumped weight based on the previous 24hrs. But to also hold each previous' 24hrs sum value.