4 Replies Latest reply on Aug 12, 2016 10:56 AM by Keshia Rose

    Make checkbox filter select sets of data that contain all checked options

    Ciprian Trinca
    Ciprian Trinca Aug 8, 2016 6:57 AM

      I want to be able to select sets of data that contain both answers.

      Right now to display those sets I have the same database view joined by MyActivityId (which represents a set of data) and using one view for data and the other for the filter. This allows me to show the full set of data in one MyActivityId.

      How can I make the filter select MyActivityIds which contain both answer and not which contain one or the other?

      Also, the solution must work for multiple selections (not just 2).

      tableau.png

        • 1. Re: Make checkbox filter select sets of data that contain all checked options
          Keshia Rose
          Keshia Rose Aug 8, 2016 3:48 PM (in response to Ciprian Trinca)

          Hi Ciprian,

           

          Here is one way you could do this.

           

          1. First, add your answer filter to context.

           

          2. Then create the following calc:

          SUM( { EXCLUDE [Answer]: COUNTD( [Answer] ) } ) = SUM( { COUNTD( [Answer] ) } )

           

          3. Place that calc on the filters shelf and keep only true. (If you only see false make sure you don't have every Answer selected)

           

          I've attached an example using superstore, let me know if this works for you.

           

          Take care,

          Keshia

          1 of 1 people found this helpful
          • 2. Re: Make checkbox filter select sets of data that contain all checked options
            Ciprian Trinca
            Ciprian Trinca Aug 9, 2016 1:58 AM (in response to Keshia Rose)

            It does not seem to work for me.. I get empty for True; on False I get the same number of rows without the calculated field.

             

            Also, I do not know if your example is correct. Here is what I found:

            So, Brosina Hoffman does not have a Chairs Sub-Category and still shows up in the graphic.

            tableau2.png

            • 3. Re: Make checkbox filter select sets of data that contain all checked options
              Ciprian Trinca
              Ciprian Trinca Aug 10, 2016 4:18 AM (in response to Keshia Rose)

              I found a way to do it.

              History:

              So, I said before I use the same view twice bound by MyActivityId so I can show sets of data for each MyActivityId. One view is for data, the second to used for the filter (because I filter on Answer but want to display full sets, not just the selected Answer rows). The filter has to select the MyActivityIds which contain all selected Answers (not all MyActivityIds which contain at least one Answer).

              Solution:

              I count the distinct Answers from my filter in Calculation2.

              Then:

              A. Then I can use Calculation2 directly by:

                        1. putting it in filters

                        2. right click: Quick table calculation: Percent of total

                        3. Then select the value to show only 100%

                   this means that only MyActivityIds which have all selected Answers in the filter will be shown.

              B. Can create Calculation3 as TOTAL([Calculation2]) = [Calculation2]

                        1. putting it in filters

                        2. select value to show: only True

                   this will compare the total number of  Answers selected (basically) with the Answers present in each set (because the simple [Calculation2] will have an AGG or aggregate value, so for each set) (TOTAL([Calculation2]) will be the distinct Answers present in the filter view/table which will be the ones selected).

              tableau3.png

               

              Thank you Keshia Rose! You pointed me into the right direction to make this happen.

              • 4. Re: Make checkbox filter select sets of data that contain all checked options
                Keshia Rose
                Keshia Rose Aug 12, 2016 10:56 AM (in response to Ciprian Trinca)

                Way to go Ciprian! Glad you found a solution that works for you!

                 

                Also, just to clarify, you will not find Brosina's chair purchases in the preview pane on the data source page because it only gives you the first 1,000 rows, unless you change the number.

                 

                Take care,

                Keshia