5 Replies Latest reply on Apr 14, 2018 1:21 PM by Branden Kornell

    Tableau Inquiry

    Hung Nguyen

      Hi,

       

      As I am working on a project for my university, I am supposed to create a dashboard for machines downtime.

      Every time that a machine is down during the day, the availability is calculated as [ 24 (hours) - Downtime / 24 (hours) ].

      However, there are some situations where the machines are down more than 1 day, so it means that I have the split the number of hours that machine is down and distributed to the respective days.

       

      For example:

      MachineReported TimeFinished Time
      Down Time (hours)
      A2018/01/01 13:00:00 PM2018/01/01 19:00:00 PM6
      B2018/01/01 12:00:00 PM2018/01/02 12:00:00 PM12 hours on Jan 1st and 12 hours on Jan 2nd

       

      For machine B, how can I split them in tableau? Would I be able to write some algorithm to do that?

      I apologize if this example is kinda ambiguous. Thank you!

        • 1. Re: Tableau Inquiry
          Branden Kornell

          Are you able to work with the source data?

           

          The natural way for Tableau to work with it would be for the second row to be split into 2 rows.

           

          You can keep the existing columns as is, but create two new columns: one for [Affected Date] and one for [Down Time on Affected Date].

           

          Unfortunately, this requires a pre-Tableau solution (for example, doing it in SQL).

          • 2. Re: Tableau Inquiry
            Hung Nguyen

            Hi,

             

            I am able to work with the data source. I can write the query in Microsoft SQL to pull the data.

            I just not sure of how to split in Microsoft SQL. I think spliting the 2nd row into 2 rows with 2 different end dates is the solution.

            Would you mind walking me through how you would do it in Microsofy SQL? Is there any other information I need to provide to you?

             

            Thank you!

            • 3. Re: Tableau Inquiry
              Branden Kornell

              Awesome, Hung. What you need to do in SQL is join to a reference table with all possible dates, one row per day.

               

              If the report will be run going forward, make sure you have enough days in the future so the join continues to work. (Don't just make your table through the end of 2018; maybe do through 2025.)

               

              The tricky parts are

              • you need to write the SQL join logic with >= and <= (or BETWEEN), rather than an = join.
              • you need to make sure you're comparing DATE datatypes, not DATETIMES.

               

              Sorry, I can't give you exact SQL Server code because the functions are a little different than what I'm used to. But it will be something like

               

              SELECT B.Date

               

              FROM Main_Table A

               

              INNER JOIN Date_Table B ON

                   B.Date >= CAST(A.Reported_Time AS DATE)

                   AND B.Date <= CAST(A.Finished_Time AS DATE)

               

              You want the tables to join for each day in the range, which will duplicate rows from Main_Table. Then you can calculate the hours during that day in Tableau.

              • 4. Re: Tableau Inquiry
                Hung Nguyen

                Hi Brandon,

                 

                Thank you so much for your help!

                I wonder how the reference table would look like? Is it like this?

                 

                Date
                1/1/2018
                1/2/2018
                1/3/2018
                1/4/2018
                1/5/2018
                1/6/2018
                1/7/2018
                1/8/2018
                1/9/2018
                1/10/2018
                1/11/2018
                1/12/2018
                1/13/2018
                1/14/2018

                 

                Also,

                INNER JOIN Date_Table B ON

                B.Date >= CAST(A.Reported_Time AS DATE)

                AND B.Date <= CAST(A.Finished_Time AS DATE)

                 

                Can you explain a little bit about the Join that you mention? How does this lead to having duplicated rows?

                Thank you so much for your support!

                • 5. Re: Tableau Inquiry
                  Branden Kornell

                  Yes; exactly right. The reference table will have one row per day.

                   

                  Can you explain a little bit about the Join that you mention? How does this lead to having duplicated rows?

                   

                  Take the second row of your example: an event that starts on 1/1/2018 and ends on 1/2/2018.

                   

                  In this case, two rows in the reference table meet the join logic: the rows for 1/1/2018 and 1/2/2018:

                   

                  • B.Date >= CAST(A.Reported_Time AS DATE) AND B.Date <= CAST(A.Finished_Time AS DATE)

                   

                  • 1/1/2018 >= 1/1/2018 and 1/1/2018 <= 1/2/2018
                  • 1/2/2018 >= 1/1/2018 and 1/2/2018 <= 1/2/2018

                   

                  That row in the main table would join to the reference table twice, and retrieve two rows in the results.

                   

                  An important thing to keep in mind in Tableau is, if you're counting events and not breaking them out by day, you will want to use COUNTD(Event_ID) rather than COUNT(Event_ID), so that you don't double-count events.

                   

                  Good luck!