2 Replies Latest reply on Jun 21, 2015 1:59 PM by kettan

    Alarm data with open/close timestamp. Need duration running per day

    Shaun Paterson

      Hi folks,

       

      I've been reviewing the FAQ about Open & Close Dates - http://community.tableau.com/thread/151387. But I cannot find exactly what I am looking for.

       

      I have live data coming from an alarm monitoring system. This is telling me, across a huge number of sites, when a generator is running or not. Occurred at - Generator starts to run and Cleared at - Generator has stopped running.

       

      For example the generator runs for a few hours, stops, runs again. The alarms are running at all times of the day and night. The example screenshot below is for just one generator on one site:

      Alarm example.png

      What I am having desperate trouble with is calculating exactly how many hours and minutes that each generator is running on a particular day. I would also like to find out the total number of hours the generator is running in a month.

       

      Ultimately, if an alarm is running from 30/05/2015 20:28 to 31/05/2015 04:09, the calculation needs to ensure that the time between 30/05/2015 20:28 and midnight is attributed to the 30th May and the period from midnight to 31/05/2015 04:09 is attributed to 31st May.


      I also have some very long running alarms, which can run for weeks. So I need to ensure that, for example if an alarm starts on 12th May and ends on 29th May, that every day between those dates is considered to have been running for 24 hours.


      At the same time if an alarm is running and has not yet closed, I need to ensure that the duration up to NOW is captured and included.


      I've attached a small CSV file with example data for one generator. I'm sure someone else has had similar problems. If anyone can point me in the right direction, it would be a big help. I'm using Tableau 9.0.


      Thanks very much... Shaun