Tally duration of events below a threshold

tally_duration.xlsxtally_duration.xlsxI have a raw data set that includes hourly pH values at multiple sites ("Sonde_Names") for two three-month periods: one in year 2013 and another in year 2014.

For each of the "Sonde_Names", I need to generate a list showing the number and duration of events where the pH is below a value of 5.5 (the field "BlwThresh" indicates which records are below 5.5). An 'event' is defined as one or more contiguous values of 1 in the "BlwThresh" field. This list also needs to include the associated values for "Sonde_Names", "Year", "Month", "Day", and "Hour", in which the event began.

(If possible, the "Event_num" field should reset to a value of 1 when starting to process each unique set of "Sonde_Names" and "Year" values.)

The worksheet named "tally_duration" shows a few manual examples of the objective.

Many thanks in advance for a (hopefully portable) automated solution.
LVL 1
dougf1rAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
You appear to have omitted the sample data; please can you upload.
0
dougf1rAuthor Commented:
Attempted to include (looks like it went in twice!), hopefully you see in on your end, thanks
0
Rob HensonFinance AnalystCommented:
Yes can now see it (twice).

I have been thinking its probably possible with a Pivot Table but no joy working it out yet.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dougf1rAuthor Commented:
Thanks for looking into this.

tally_duration_magnitude.xlsx
Here is an update to the original example calculations to also include the "Total Magnitude" and "Average Magnitude" of each event (see yellow highlighted fields in the attached).
0
Rob HensonFinance AnalystCommented:
See attached with a couple of helper columns on the raw data and then a Pivot Table on the tally_duration sheet.

Helper columns:
Start
=IF(N2=0,"",IF(AND(F2=F1,P1<>""),P1,IF(N2=1,G2,"")))

This forms a column of numbers or blanks where the number is the first hour of the event when the BlwThresh flags as a 1. The check for the day starts again where the continuous run of 1s crosses a daybreak.

Duration
=IF(P2="","",IF(AND(F2=F1,Q1<>""),Q1,MATCH(0,N3:N$14303,0)))

This looks at the Start column and if blank returns blank. If the day in the row is the same as the previous row and previous row duration isn't blank it takes the previous row value otherwise it looks in column N for the next 0 value and counts the number of rows between itself and the next 0.

The whole data set including the helpers has been then been used to create the pivot table on tally_duration.

The BlwThresh column has been used as a Page Filter and filtered to show only the 1 values. I have also filtered on Sonde Names just to show the same as the sample.

The Hour column has been replaced with the Start helper column but can be renamed as Hour if so required.

The duration is then set as a count in the value field, could also use Min, Max or Average but not Sum.

Event Number???? Still looking at that, might have to be a count outside of the pivot.
tally_duration.xlsx
0
dougf1rAuthor Commented:
...it would also be best if another field can be included that lists the "Maximum Magnitude" as well. So, the  SUM, AVERAGE, and MAX of the magnitude values for each event is what is needed.

(apologies for the piecemeal question)
0
Rob HensonFinance AnalystCommented:
Updated file with the SUM, MAX and AVERAGE added to the pivot table.
tally_duration.xlsx
0
dougf1rAuthor Commented:
Thanks, Rob.

This is close, but gets tripped up when the event spans multiple days (this single continuous event shows up as 2 or more events on different days).
0
Rob HensonFinance AnalystCommented:
Can you confirm how a continuous event that spans a date break should be shown.

Example that I have found is row 722 on the data sheet. Event started at 22:00 on 07/11/2013 and continued for 3 hours through to 01:00 on 08/11/2013. Is this one event or two?

I have just updated the pivot and it now shows as one event starting at 10 lasting 2 hours on 7/11 and one event starting at 0 and lasting 1 hour on 8/11.
0
dougf1rAuthor Commented:
The event that begins on row 720 and ends on row 722 is a single event (even though it spans two different days)
0
Rob HensonFinance AnalystCommented:
OK, I will take out the day change check.

I think I have a method for the event numbering, still WIP so will upload when I am happy with it and the above correction.
0
dougf1rAuthor Commented:
Ok

Keep in mind, that it is possible that the event can also span 2 different months
0
Rob HensonFinance AnalystCommented:
Yep, that's not an issue. The Start column now just looks at previous row for a value or blank. If value it continues as the same otherwise it checks for the start of a new event.
0
Rob HensonFinance AnalystCommented:
Here is the latest version, go the Event numbering in it but its not quite working correctly when Event spans a day break.
tally_duration.xlsx
0
dougf1rAuthor Commented:
Thanks again Rob, it seems that something isn't quite right. There is an event for the "WolfLaurel" site that begins on Nov 26, 2013 (starting at 5am) that spans 100 hours. This is showing up in the Pivot Table as five different events, each with 100 hour duration.
0
Ejgil HedegaardCommented:
To make it span over years, months and days, 3 extra columns could be added to hold the values for year, month and day, and those columns used in the pivot.

Or a macro can make the list.
See file.
tally_duration.xlsm
0
dougf1rAuthor Commented:
Thanks, Ejgil. This macro works really well for getting the duration of each event.

In one of the comments, I had also asked about including the SUM, AVERAGE, and MAX of the set of values listed in the "MagBlwThresh" field associated with each event. Would you be able to modify the macro to include these values for each event?

(Note: Including 3 extra columns to hold the values for year, month and day in the pivot resulted in splitting single events that occurred over multiple days or months into multiple events, which was not desired)
0
Ejgil HedegaardCommented:
Sum, Average and Max added.

The 3 columns must have formulas like Start (Hour).
See file.
tally_duration.xlsm
tally_duration-pivot.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dougf1rAuthor Commented:
Many thanks for your contributions!
0
Rob HensonFinance AnalystCommented:
Sorry I dropped the ball on this one. Been unwell for a couple of days and hadn't come back to it.
0
dougf1rAuthor Commented:
No worries, was running out of time on my end. Thanks for your help and hope you are feeling better.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.