
1. Re: Running Sum, restart every time line crosses x axis
David Li Oct 25, 2016 9:00 AM (in response to Sean Bleything)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 xaxis, 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
Sean Bleything Oct 25, 2016 12:12 PM (in response to David Li)This is exactly what I was looking for, thanks!

3. Re: Running Sum, restart every time line crosses x axis
David Li Oct 25, 2016 12:18 PM (in response to Sean Bleything)You're welcome!

4. Re: Running Sum, restart every time line crosses x axis
David Li Oct 25, 2016 12:21 PM (in response to David Li)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 datetime 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
Sean Bleything Oct 25, 2016 12:24 PM (in response to David Li)That makes sense, thank you again.