5 Replies Latest reply on Oct 25, 2016 12:24 PM by Sean Bleything

# Running Sum, restart every time line crosses x axis

I am trying to figure out how to calculate a running sum of hours that a metric is either positive or negative.  Attached is a workbook that contains a year worth of utility data.  It show when there is an oversupply of energy (positive) and undersupply (negative).    I need to find a way to add up the consecutive hours each time the line is positive, then reset when the line goes negative (and sum those consecutive hours).  I'm not exactly sure how to best visualize the results, but at a minimum, it would be good to know:

1. what is the longest period of consecutive hours of oversupply?

2. what is the longest period of consecutive hours of undersupply?

3. a bin of those results (how many instances of 1 hour of oversupply, 2 hours of oversupply, 3 hours, etc., and the same for negative)

For extra credit, I also would like to calculate the area under each arc.  Instead of just summing the hours of consecutive oversupply/undersupply, I would like to calculate the total MW hours for each period of oversupply/undersupply.

I have attached the workbook - can anyone help?

• ###### 1. Re: Running Sum, restart every time line crosses x axis

Hi Sean! Here's something to get you started:

I used this calculation to get the running total of hours of under- and oversupply. When it's undersupply, it's a negative number; when oversupplied, positive. The absolute value is the number of consecutive values.

IIF(SUM([Imbalance]) < 0, -1, 1) + IIF(SUM([Imbalance])*LOOKUP(SUM([Imbalance]), -1)>0, PREVIOUS_VALUE(0), 0)

Doing WINDOW_MAX() of this calculation gives you the max consecutive oversupply, and doing the WINDOW_MIN() gives you the max undersupply. You can also kind of see it from the color in the graph. Using the tooltips is the best way to interact with this particular viz.

This calculation gives you the running sum integration between the curve and the x-axis, restarting every time it crosses it:

SUM([Imbalance])+IIF(SUM([Imbalance])*LOOKUP(SUM([Imbalance]), -1)>0, PREVIOUS_VALUE(0), 0)

If you'd like to use this in another kind of view, e.g. a table, then it gets a bit more complicated, but we can talk about that if needed.

• ###### 2. Re: Running Sum, restart every time line crosses x axis

This is exactly what I was looking for, thanks!

• ###### 3. Re: Running Sum, restart every time line crosses x axis

You're welcome!

• ###### 4. Re: Running Sum, restart every time line crosses x axis

Oh, and I forgot to mention that this is only completely accurate if you have a densely populated data source where every hour is recorded. If there are any gaps in the hours recorded, you could get incorrect consecutive hour calcs. I tried to prevent such issues by using "Show missing values" on the date-time pill, but it's best to just make sure that the data is densely populated.

• ###### 5. Re: Running Sum, restart every time line crosses x axis

That makes sense, thank you again.