4 Replies Latest reply on Oct 24, 2013 8:56 AM by caitlin.donaldson

    Limiting results by presence of a particular type of event

    Marc Engle

      Hello,

           I'm working on an analysis of when projects went on hold or got cancelled during their lifecycle.  I have a view that shows the timeline (starting from 0 months) with the different events that happened the project, along with a bar representing the intended length of the project from beginning to end of development.  If the user clicks on the project number in the top view of the dashboard, they will get a detail view below with each event on a separate line.

       

           I'd like the user to be able to choose from whether they want to show all projects, or only projects that have Hold or Cancel events.  I can get it to filter to those projects that have the Hold or Cancel events, but every time I do, I lose all events that are not of that type.  My desire is to only filter the list of project numbers down to the ones that contain hold or cancel events but, for those projects, be able to show all the events related to the project.

       

          I feel like there's a table calculation solution to this, but have not been able to get WINDOW_COUNT or WINDOW_SUM to work right by then using the result as the filter.

       

           Any help would be greatly appreciated.

       

      Regards,
      Marc

        • 1. Re: Limiting results by presence of a particular type of event
          Marc Engle


          Thought I'd give a quick bump to this one in case it had fallen off the ever so quickly growing list of posts.  I'm still struggling with this particular problem.  Thanks for taking a look.

          • 2. Re: Limiting results by presence of a particular type of event
            caitlin.donaldson

            This is a tricky one I've struggled with myself.  There might be a better way to approach this situation than what I've found, but I have started creating Sets for those projects (or in my case users) that fit a certain criteria.  Then I use the Set to filter data.  I've also used table calculations before, but those can get cumbersome if you have a lot of rows. 

             

            If you aren't importing the data directly into Tableau from something like an ODBC, another way to do it is to attach a new variable signifying whether it has a Hold/Cancel event or not to the data before bringing it into Tableau.  Basically, create the list of projects that have a Hold or Cancel event (just the name or identifier of the project, nothing else), then create a new variable to add to your original set of data.  In other words, you would create the list, create another column that says something like "Yes" or "True", and then use Access or something similar to join your original data set with the list of projects with the given criteria.  That way you'll have a column with a null if there is no Hold or Cancel event and a "Yes" or "True" if there is.  In this case, make sure to setup the join in a way where all of the original data is kept and Holds/Cancels only join where the project identifier is equal in both tables (so a left join).  Otherwise, you could lose any rows where a project does not have a Hold/Cancel.

             

            I hope this helps!  Let me know if any of this is unclear.  I'll make sure to follow this thread in case anyone has a more comprehensive way of dealing with this situation since I run up against this problem fairly regularly.

            • 3. Re: Limiting results by presence of a particular type of event
              Marc Engle


              Caitlin,

                   Thanks so much for the help.  I, too, had considered going outside of Tableau to do this in the SQL datasource that I'm using, but was trying to figure out a way to do this inside of Tableau instead of outside.

                   I had not considered Sets, as they aren't my typical go to piece of functionality.  I was able to get this to work using that method, so thank you very much.  It looks like that will quickly become a more used tool in my toolbox.

               

              Regards,
              Marc

              • 4. Re: Limiting results by presence of a particular type of event
                caitlin.donaldson

                Happy to help!  Glad to hear that method worked for you.  I'm hoping a future release at some time will make data aggregation situations like this more straightforward and easier, but for now at least there are a few ways to get around it.