3 Replies Latest reply on Nov 28, 2018 1:23 AM by Shiva Pillai

    Filter related tricky question

    Shiva Pillai

      Hello Experts,

       

      I have a question around filtering in Tableau for a scenario that I have described below. I am sorry, I can't attach a sample workbook here. There are IT restrictions in my organisation that prohibits users from uploading any data to third party websites.

       

      I am creating a view of all open findings in my business. Each finding can have actions that are either open or closed. There are also some open  findings where all the actions are closed - this is precisely what I am struggling to show in the view when I apply filter on actions.

      A finding can have either multiple actions or only 1 action.

       

      I want to create a view that shows all open findings as well as open actions. This is simple - I just create a table and add a filter for Action Status and select 'Open'. In the example below this will contain the details of Finding ID A1-10/2017 as well as underlying actions 1 and 4 that are open for that finding ID.

      However, if I use the above filter finding IDs B1-11/2016 and C1-10/2017 will not appear in the view as all the actions are closed (i.e. there are no Open actions as per the filter). Is it possible to modify the action filter such that it shows the details of findings where there are no open actions, despite having a filter only for open actions, because the finding is still open?

       

            

      Finding IDFinding StatusFinding Target DateAction IDAction StatusAction Target  Date
      A1-10/2017Open31/10/20191Open31/07/2018
      2Closed30/06/2018
      3Closed30/04/2015
      4Open30/09/2019
      A2-10/2017Open31/12/20182Open30/11/2018
      3Open30/09/2018
      B1-11/2016Open31/12/20181Closed30/06/2018
      2Closed30/07/2018
      3Closed30/08/2018
      B3-12/2017Open30/11/20181Open31/10/2018
      C1-10/2017Open31/01/20194Closed31/12/2018

       

      Thank you folks,

      Shiva

        • 1. Re: Filter related tricky question
          Amit Narkar

          Count of Actions= { FIXED [Finding ID]:COUNTD([Action Status])}

          Filter=

          [Action Status]='Open' OR ( [Count of Actions]=1 AND [Action Status]='Closed' AND [Finding Status]='Open')

           

          Results Appear as below

          1 of 1 people found this helpful
          • 2. Re: Filter related tricky question
            Shiva Pillai

            Hi Amit,

            Thank you for the quick response. It helped me partially. The count of Actions formula doesn't always give the correct count. For instance in the example above, Count of actions is 1 for A2-10/2017 AND B1-11/2016 despite there being 2 open actions and 3 closed actions respectively.

            I will try to expand the formula that you have provided and see if I can get the desired result.

             

            Regards,

            Shiva

            • 3. Re: Filter related tricky question
              Shiva Pillai

              Please disregard my earlier post Amit. I see what you have done here and it makes perfect sense now. So effectively the formula will hide all closed actions (if there are multiple actions some with open and others with closed status). If there are multiple actions all with closed status, it will keep all the closed actions in the view. Same for multiple actions with open status.

              I guess this would work for me. Thanks again.