1 Reply Latest reply on Oct 8, 2018 10:01 PM by swaroop.gantela

    Help With Date Time and Seconds wrt binary data.

    Rahul Singh

      Hello Forum,

      I've been lurking for a few hours trying to figure out the best possible solution to my problem.

      I am an amateur so please don't mind if the question sounds silly.


      1. I have a very simple data set (simpler than the data set attached in the file). I need help with two columns, time stamp and ON/OFF column. The data set is from a sensor.
      I need to visualize the time duration the sensor has been on or off. The formula I used for calculating the time difference is:


      ABS({FIXED [Hour Back Door Contact] :

      DATEDIFF('second',MIN(IF [Value]= 'ON' THEN [Time Back Door Contact] END),

      MIN(IF [Value]= 'OFF' THEN [Time Back Door Contact] END))



      And I plotted this against the date and time column. The problem, the viz is neither aesthetic,nor does it say where the sensors are on or off in any comprehensible term. All this is doing is giving me information on how long the sensors were up.

      All in all, I'm not sure how to represent this data more aesthetically.


      2. I also need to add a provision to filter out time duration that can be used to answer questions like, show me the motion data for last 4 hours/ last one day/ last month etc. Parameters would be required for that I assume but anything more I should be aware of?


      3. The actual data is coming from a server and I have multiple sensor's data set. I think visualizing them all into one chart would be better and having a filter for selecting a specific sensor. I think I will have to use UNION for that?


      Any kind of guidance will really save me a lot of time.


      Thank you for reading, have a nice day!

        • 1. Re: Help With Date Time and Seconds wrt binary data.



          I think you could try a Gantt Chart visualization of your data.

          Here is a thread vaguely similar to yours that used the Gantt chart.

          Time Calc


          I'm not sure if this will suit your setup, but maybe it can give ideas.


          The bar length can be calculated by comparing the time until the next state:

          IF LAST()=0 THEN 0   // if last date, no bar

          ELSE DATEDIFF('second',ATTR([timeForGantt]),LOOKUP(ATTR([timeForGantt]),1))/(60*60*24)     // otherwise just time til next point



          There are very specific Table Calculation settings needed for the bar length

          and the total time on/off (please see screenshot below).


          Please see workbook v18.1 attached in the Forum Thread.