7 Replies Latest reply on Jan 9, 2014 3:28 PM by Jonathan Drummey

    Create Dynamic Set

    Allison Suarez

      I have a set of data that I am trying to filter using a set.  There are three document types: DRA, PRA, and SRA.  I want to create a set that only show those documents that have a PRA but no DRA.  I have attached a workbook that shows the data.  I can see it but can't create a dynamic set, as this data updates frequently.  Some Document numbers that should show in the resulting set are:

      10000242201, 10000242202, and 10000242526.

      Any suggestions would be appreciated.



        • 1. Re: Create Dynamic Set
          Shawn Wallwork

          Allison, I really don't think you need to use a set. The filter you have should work fine, just uncheck DRA and you'll have all the records you want. As to updating, whenever you refresh your data the filter will automatically eliminate (hide) all the DRAs. Or am I completely misunderstanding what you are trying to accomplish?



          • 2. Re: Create Dynamic Set
            Allison Suarez

            Not quite.  If i remove the filter I will see everything that has a PRA.  What I need to see is everything that has a PRA and not a DRA.  Does that make sense?

            • 3. Re: Create Dynamic Set
              Shawn Wallwork

              OK, it was a brain freeze. You can have multiple instances of Document numbers (with different status). Give me a second I'll look a little closer.

              • 4. Re: Re: Create Dynamic Set
                Shawn Wallwork

                So I still contend you don't need to do anything special to get the result you are looking for, keep the filter on the filter shelf and only check PRA (see the first tab in the attached workbook). This gives you 161 records. Now take a look at the second tab, where I created this calculation setting the filter as you requested:


                [DRA/PRA/SRA]='PRA' AND [DRA/PRA/SRA]<>'DRA'


                ... and took the other filter off the shelf. Note in the bottom left corner, there are only 161 records. Generally speaking it would be better to do this as a filter, than a calculation (in my experience, given my knowledge of the product, etc.).


                Or am I still misunderstanding your need?



                1 of 1 people found this helpful
                • 5. Re: Create Dynamic Set
                  Jonathan Drummey

                  Shawn's off on holiday sailing so I'm jumping in on this.


                  The challenge here is that you need to compare different rows in the underlying data for each Document #. To do that in Tableau, we either need a regular aggregate calc or a table calculation. In this case, since the desired level of detail for the view is the Document # (and we don't need the DRA/PRA/SRA field), we can use a regular aggregate calc.


                  In the attached, I set up a PRA with no DRA Flag calc, here's the formula:


                  ZN(SUM(IF [DRA/PRA/SRA] == "PRA" THEN 1 END)) > 0 AND ISNULL(SUM(IF [DRA/PRA/SRA] == "DRA" THEN 1 END))


                  The first part does a sum of the PRA rows and looks for that sum > 0, the second part looks for DRA rows and wants that sum to be Null (i.e. there are no rows with DRA). This way, we don't need the PRA/DRA/SRA in the view. The calculation itself can be put into the the Formula part of a Conditional Filter on Document #.


                  To validate this, I set up the "grouped data" worksheet where I found some (but not all) document #s that had a PRA but not DRA and put them into an ad hoc group. Then I put the calc into a view without the PRA/DRA/SRA in the view to validate it, and finally the last worksheet shows the Conditional Filter in place.



                  • 6. Re: Create Dynamic Set
                    Allison Suarez

                    Worked like a charm.  Thank you, it was driving me nuts!

                    • 7. Re: Create Dynamic Set
                      Jonathan Drummey

                      You're welcome!


                      Sent from my iPhone