6 Replies Latest reply on Jan 28, 2013 12:53 PM by Richard Leeke

    Creating a timeline for equipment performance

    Derek Noble

      Hi,

       

      This might already have been addressed but since I don't know what to look for I am finding it difficult to find anything helpful.

      I have a MySQL table with records that show when equipment are on or off and I would like to show this in a visualisation as a straight horizontal broken line that is present when the equipment is on and missing if its not.

      The data is not in the same record though, there will be a record with a time-stamp with a state=1 (ON) and another with a state=0 (OFF).

      I need to show a line between the ON time and the OFF time.

      I have attached a spreadsheet with some sample data in the same format. There are multiple sources and difference services for each source.

      I have no idea how to approach this as I am a new Tableau user so any advice is greatly appreciated.

       

      thanks,

       

      Derek

        • 1. Re: Creating a timeline for equipment performance
          Shawn Wallwork

          Derek, you seem to be describing a Box Plot type chart. There are lots of resources out there describing how to accomplish this, even a video showing you how to do this. Also, you'll get a much better result if you use Dustin's new search tool. Here's "Box Plot". Hope this helps.

           

          --Shawn

          • 2. Re: Creating a timeline for equipment performance
            Derek Noble

            Hi Shawn,

             

            Thanks for taking the time to answer.

            I have looked at the video link you gave me and its not what I am after.

            I am looking to display a horizontal line which shows when a device is on and a gap in the line to show when its off.

            The problem I have it that the data I have is not all in one record. its one record for ON and a separate record for OFF.

            If the ON and OFF time-stamp is in the same record I would use a datediff and get a duration and the line would represent the duration but I don't have the ON and OFF timestamps in the same record.

            • 3. Re: Creating a timeline for equipment performance
              Joe Mako

              I am not sure I understand the logic you are looking for, but is the attached closer to want you want?

              • 4. Re: Creating a timeline for equipment performance
                Richard Leeke

                Just a couple of slight tweaks to Joe's workbook, which might be helpful. I use this sort of representation extensively - often for exactly what it looks as if you are doing (I assume GPRS means this is cellular data services).

                 

                It's worth turning on mark borders to highlight places where two "on" periods occur in quick succession - otherwise you can think you have a continuous "on" period as the marks run together, but with very short bars the borders an end up swamping the mark colours. Here is the same snippet of Joe's chart with no border, a black border and a white border. Note how the white border can completely lose small marks.

                 

                No border:

                No Border.PNG

                Black border:

                Black Border.PNG

                White border:

                White Border.PNG

                 

                It may also be worth grouping all the services for a source together, to highlight concurrent service usage.

                 

                Multiple Services.PNG

                2 of 2 people found this helpful
                • 5. Re: Creating a timeline for equipment performance
                  Derek Noble

                  Thanks Guys. This seems to be exactly what I am looking for.

                   

                  I have one more question if that's OK:

                  I have more sources of data that I would like to add to this chart so I can compare.

                  This data originated from different equipment and is not formatted like the data I originally attached to this question.

                  This data has a start date/time and a duration in seconds.

                  How would I add this to the above as, lets say, a 'service 4' and 'Service 5'.

                  I have attached an example of the data.

                   

                  thanks,

                  • 6. Re: Creating a timeline for equipment performance
                    Richard Leeke

                    The easy way is to produce a second, equivalent, chart and combine them on a dashboard. The way your additional data is formatted makes it much easier than the original (no need for table calculations to correlate times between rows). The only trick to remember is that the duration needs to be expressed in (fractional) days - since the timeline is a datetime which is represented internally as fractional days. I've attached a quick example.

                     

                    If you want to combine them both on the same chart then you really need to transform one or other data set so that they are both in the same structure, and then combine the raw data. Depending how you get your data and how it is stored (text file, database, etc) there are a number of ways to do that.