3 Replies Latest reply on Jul 17, 2018 1:10 PM by Michel Caissie

    Filtering by Condition Problem

    Scott Robinson

      Hello,

       

      I am fairly new to Tableau.

       

      I am a Helpdesk Supervisor and I am trying to create a report that will filter a data table.  We have Incident tickets and then under those same incidents there are Work Order tickets created when tasks need to be assigned to other groups.  I am trying to show only open Incidents where all corresponding Work Orders are closed.  I have attached a packaged workbook so you can have a sample of the data.  Below are a couple of quick screenshots to detail my problem.

       

      This visual shows the total number of WO's per Incident, as well as how many are currently open and closed.  To detail my issue, the Incident in red shows there are currently 2 open WO's  and 2 closed WO's for the Incident.

       

      The below visual is supposed to show only Incidents where all corresponding WO's are closed, and should not display any when there are any open WO's.  You can see below that it is still showing the above Incident, even though there are still 2 open WO's.

      In my attempt to make this work properly, I created a formula to add to the Filters card, and filter out the "Hide" selection.  I have been playing with this for quite a while, but have largely unsuccessful.

       

      Open WO Filter formula

      IF [# Open WO] > 0

      THEN

      "Hide"

      ELSE

      "Show"

      END

       

       

      Any help with this would be appreciated.

       

      Thanks in advance!

        • 1. Re: Filtering by Condition Problem
          Michel Caissie

          Scott,

           

          I suggest the following calculation for the filter shelf.

          IncidentHaveNoOpen =

          {FIXED [Incident #]: MAX( if [# Open WO] > 0 then 1 else 0 end )} = 0

           

          So for each row of an incident,  if Open is > 0  return 1,   then get the MAX of this value for all rows of the incidents,   if this MAX = 0   return true.

           

          As you can see on the sheet ValildateData , this return false  for every row of Incident 704199, while you previous calculation returns Hide for only two of the 4 rows of this incident.

          Your filter returns a per row value, while using  the lod {FIXED}  function , we get a per  Incident value that is applied on every row of the incident.

           

          You can see the final result on WO Tally (2).

           

          Michel

          1 of 1 people found this helpful
          • 2. Re: Filtering by Condition Problem
            Scott Robinson

            Michel,

             

            Thank you very much!  This worked for me.  As a side note, when I applied this formula in my actual data, it didn't like that I was aggregating an already aggregated calculation, so I had to remove MAX from the calculation to make it work.

             

            Thanks again.

            • 3. Re: Filtering by Condition Problem
              Michel Caissie

              If  #Open WO  is already an aggregation then you could simplify with

               

              {FIXED [Incident #]:  [# Open WO] } > 0