5 Replies Latest reply on Jul 29, 2013 4:37 PM by John Mogielnicki

    Count In-flight Events

    John Mogielnicki

      I am trying to have Tableau count the number of events that are active during a given time-frame given a start date and an end date, and I can't figure out how to make it work.  Can Tableau do this?  See the attached workbook for a diagram of what I am trying to accomplish.  I'll also insert the pics here.

       

      Here is the (simplified) dataset I'm starting with:

      Dataset.PNG.png

       

      And here is my desired outcome:

      Desired Output.PNG.png

       

      Thank you,

       

      John

        • 1. Re: Count In-flight Events
          Joshua Milligan

          John,

           

          It can be done with Tableau.  The definitive thread on this type of solution is here:  http://community.tableau.com/message/198288#198288

           

          However, it is a rather complex solution and if there is any way to shape the data so it looks like the table in your desired outcome, it will be quite a bit easier to work with in Tableau.  What is your initial data source?

           

          If after going through the thread above you have any questions, I'd be happy to help!

           

          Regards,

          Joshua

          • 2. Re: Count In-flight Events
            Shawn Wallwork

            John,

             

            I worked on this a bit this morning because I was pretty sure it could be done with Joe's 2-record scaffolding structure and a data blend. Turns out my assumption was correct, but I didn't get it completely worked out without doing a Hang Out with Joe Mako. I missed the bit about duplicating the data source and then blending one on the start dates and the other on the end dates.

             

            Here's the result you were looking for:

             

            Scoffolding.png

            Here's a step-by-step description of how Joe got there:

             

            1. Create an Excel worksheet that looks like this:

                      1-2-3.png

            2. Connect to this worksheet

            3. Create a Start Date parameter with current value set to 1/1/2013

            4. Create an End Date parameter with a current value set to 1/20/2013

            5. Create a calculated field named Date:

             

            CASE [Record]
            WHEN 1 THEN [Start Date]
            WHEN 2 THEN [End Date]
            END
            
            
            
            
            
            
            
            
            
            

            [Or you could do it as an IF/Then or IIF for that matter]

             

            6. Put this Date calc on the row shelf as a discrete date.

            7. Right-click and check the "Show Missing Values":

            Show Missing Values.png

            Because of Data Densification we've turned 2 records/rows into 20:

             

            Data Dense.png

            Now that we've built the scaffolding, we're ready to populate it with data.

             

            8. Duplicate the data source, renaming one of them In-Flight Starts and the other In-Flight Ends (or something similar)

            9. Go up to Data\Edit Relationships and set up custom relationships so that the Date in 2-Record Scaffold is linked to Start Time in the In-Flight Starts data connection.

            10. Do the same for the In-Flight Ends data connection. (We'll have to edit this later, but normally we won't.)

            11. Staying in the 2-Record Scaffold (primary) data source, create a calculated field named Starts containing this:

             

                      SUM([In Flight Data Starts].[Number of Records])

             

            12. Now create another one named Ends containing this:

             

                      SUM([In Flight Data Ends].[Number of Records])

             

            13. Now we need to calculate the # of Events that are open on each date. Create a table calculation containing this:

             

                      RUNNING_SUM(ZN([Starts]))-RUNNING_SUM(ZN([Ends]))

             

            Set the Default Table Calculation to compute using Date. Also pay attention to the ZN() function, otherwise the calc won't fully populate the scaffold. Drop this on the text shelf and you should end up with this:

             

            First Try.png

             

            We are close but it isn't the exact figures you were looking for. Your expected results were shifted down one row. You wanted to include the end date in the count.

             

            14. To fix this Joe went to the In-Flight Data Ends data connection and created a calculated field named Shifted End containing this:

             

                      DATE(DATEADD('day',1,[End Time]))

             

            One of the reasons I love watching Joe use Tableau is I learn little stuff along the way. For instance why did he wrap the DATEADD() function in a DATE() function? [Answer at the bottom of the page.]

             

            15. Now you'll need to go back up to the Data\Edit Relationships and edit the In-Flight Data Ends, changing it from Date = End Time to Date = Shifted End:

             

            Shifted.png

             

            That's it, now your numbers will be as requested.

             

            16. Duplicate the sheet and turn it into the line chart you wanted.

             

            John, thanks for presenting us with such a well-asked question. You showed your problem, gave us very specific examples of the expected outcome and included a packaged workbook. We all wish everyone asked such well formed questions. It was a pleasure working it out (well watching Joe work it out). Thank you.

             

            Answer: DATEADD() returns a Date-Time date type that you then need to manually change to a Date date type. DATE() returns a Date date type that doesn't need to be changed. The other little short-cut I learned watching him work last year is that if you highlight the entire line of code and then double-click the DATE() function in the list T will wrap the whole line in the DATE() function correctly (NOT delete the whole line and replace it with the function, as most Windows programs would do).

             

            Someone should do a blog about all the little time-saver techniques.

             

            Anyway, hope you found this helpful and educational, I know I did -- thanks Joe!

             

            --Shawn

            • 3. Re: Count In-flight Events
              Richard Leeke

              This thread about utilisation rates and this one on queue depth that it refers back to discuss another way of doing this sort of thing.

              • 4. Re: Count In-flight Events
                Jim Wahl

                Shawn,

                 

                Thanks for the nice example of scaffolding. When I was going through it I noticed that I had to change the data type of the Start Time / End Time in the original data source(s) to Date rather than Datetime for the blend to work. For anyone else not seeing the expect result after step 13, this might be the reason. ...

                 

                Jim

                 

                2013-07-28 15-38-29.png

                • 5. Re: Count In-flight Events
                  John Mogielnicki

                  Wow, thank you so much to all who responded.  I've never seen so many people jump in to give such a comprehensive answer to a question.  Now all I have to do is decide between my multitude of options. 

                   

                  Thanks!

                   

                  John