7 Replies Latest reply on Apr 20, 2018 5:28 PM by swaroop.gantela

    Including and Excluding Filter values based on a particular criteria

    Kapil Lehar

      Attached is the mock data on which we are trying to do the following :

      We have Configuration Id : which is a part of a car like dashboard of the car or tyres etc. Then we have part attachments which tells you about the parts that make up an entire configuration. For eg : 'head lights', 'rear lights' and 'fog lamps' make up 'Lights'.

      enter image description hereProblem Statement : We need to have 2 filters : Part attachment included and excluded such that, when part attachments included is selected as steering then I get 3 rows(marked in yellow in column E) based on selection. But in the part attachments excluded filter, we don't want the configuration id where the part attachment = 'Headlight'. This means that row no 17 should be eliminated when filtering out headlamps from the filter.

      So basically when part attachment excluded filter excludes 'headlight' then data in row no 17(etc : steering) should not be shown in tableau.

      I found this a little difficult to implement using normal filters in tableau.

        • 1. Re: Including and Excluding Filter values based on a particular criteria
          swaroop.gantela

          Kapil,

           

          If your dataset will allow for a self-join, you may be able to try something as follows:

           

          With this method, I think you will only need once column of Parts.

          First will need to Self-join dataset on [Serial NO]

          so that you will get every combination of Parts for that Serial No.

           

          Now can rename [Parts] from original copy to [Part Incl]

          and rename [Parts] from second copy to [Part Excl]

           

          [Part Incl] goes on the filter shelf in the regular way,

          but [Part Excl] is put on the filter shelf with the "Exclude" box checked.

           

          Setting [Part Incl] to 'steering' will pull up all three instances,

          and then setting [Part Excl] to 'headlights' will remove the one row

          that has 'steering' as [Part Incl] and 'headlight' as [Part Excl].

           

          Now the goal is to count how many [Part Incl] there are in the original set

          and see if that matches the number [Part Excl] in the second set. If they

          do not match, then situation has occurred where the Serial No. had

          both 'steering' and 'headlight'

           

          This check is done as follows:

          { FIXED [Serial NO]:COUNTD([Part Inc])}=={ FIXED [serialNO (Sheet11)]:COUNTD([Part Excl])}

           

          Please note in the workbook attached in the Forum thread that the [Part Excl] filter has been added

          to the context, but [Part Inc] has not been added. This allows the comparison between the original

          set (unaffected by context filter) and the filtered set (with context filter).

          • 2. Re: Including and Excluding Filter values based on a particular criteria
            Kapil Lehar

            Hi Swaroop,

             

            I appreciate your response.

             

            However I have created self join and the filters as mentioned by you, but couldn't proceed further as the exclude filter doesn't work as intended

             

            My excel file and the steps that i followed as per your explanation(twbx file) is attached here.

            • 3. Re: Including and Excluding Filter values based on a particular criteria
              swaroop.gantela

              Kapil,

               

              I think it was generally set up correctly, but I think your workbook

              was missing the [CheckToKeep] filter which I added and placed on the Filter shelf.

              The calculated field is:

              { FIXED [Serial No]:COUNTD([Inc])}=={ FIXED [Serial No (Parts1)]:COUNTD([Exc])}

               

              Also, the [Exc] needed to be added to the context (via right-click on the pill on the filter shelf).

               

              Please see the attached in the Forum thread.

              • 4. Re: Including and Excluding Filter values based on a particular criteria
                Kapil Lehar

                Why have we put exclude in the filter shelf ?

                 

                Also Can you explain the quoted text here :

                 

                Now the goal is to count how many [Part Incl] there are in the original set

                and see if that matches the number [Part Excl] in the second set. If they

                do not match, then situation has occurred where the Serial No. had

                both 'steering' and 'headlight'

                • 5. Re: Including and Excluding Filter values based on a particular criteria
                  swaroop.gantela

                  Kapil,

                   

                  I may not have correctly understood the requirements.


                  What I was working towards was having:

                  -a first filter to pull those Serial No. that contained the selected inclusion part

                  -a second filter to remove those Serial No. that contained the selected exclusion part

                   

                  For that case, the Exclusion part was placed on the Filter shelf with the "inverse"

                  setting of excluding the items that are selected.

                   

                  After the self-join, there are two versions of the parts list.

                  Only in one version is a part excluded.

                  If the count of parts in both versions is the same, keep that Serial No.

                  If the count of parts is different between versions (something was excluded), exclude that Serial No.

                  • 6. Re: Including and Excluding Filter values based on a particular criteria
                    Kapil Lehar

                    Hi Swaroop,

                     

                    Thanks again. This is what we wanted.

                    Unfortunately as far as our real client data is concerned , we wont be able to implement this since its already close to explosion. By joining each part attachment with other part attachment(which are 100s in number) we will be significantly increasing the number of rows.

                    • 7. Re: Including and Excluding Filter values based on a particular criteria
                      swaroop.gantela

                      Kapil,

                       

                      I'm sorry that it won't work for the dataset.

                       

                      A suboptimal alternative is to use a parameter

                      for the Exclude part. But the parameter is not dynamic.

                       

                      Will keep looking into other ways to have the include part

                      also know what was excluded.

                       

                      Opening to the Community for other methods.