4 Replies Latest reply on Oct 4, 2018 10:43 AM by Gene Kucinkas

    List items excluded from filter

    Gene Kucinkas

      version 10.2.12


      Think I am missing something pretty straightforward but can't find an answer or come up with my own. I have a list of items that is filtered and needs to have multiple values selected (ie parameters won't work to my knowledge). The list is pretty long so needs to be a drop-down filter. Majority of the time only 3 - 5 items will be excluded depending on user's needs.


      I would like to show the names of the items that have been excluded in the caption or in a pane somewhere. Have tried several different approaches for calculated fields but not come up with any solutions. Feels like this is kind of the opposite of what filters are made to do which is why it's a weird case.


      Attached a sample book with a list of names, assuming the nature of the data doesn't matter too much for this question. Field I'm using is also a string.


      Thanks much for any help or advice!

        • 1. Re: List items excluded from filter
          Peter Fakan

          Hi Gene, do you mean like this ?


          Just duplicate the sheet, and change the filter to exclude values



          HTH 18.2 attached



          • 2. Re: List items excluded from filter
            Hari Ankem

            Would this help?



            If yes, then I have basically joined the data with itself as shown below, and created the above calculated field to identify the excluded value and display in the title.


            • 3. Re: List items excluded from filter
              Okechukwu Ossai

              Hi Gene,


              I guess you want the excluded list to update each time you update the filter. If yes, you might have to change the structure of your data. This will duplicate the size of your dataset. One approach to minimize the extent of such duplication is to union the data. For optimization purposes, you can use this 'unioned' datasource for this analysis only and then do the rest of your reporting on a separate single datasource.


              Step 1: Union the data


              Step 2: Create calculated field [Name Filter]

              IF [Table Name] = 'names' THEN [Names] END


              Step 3: Duplicate Name filter and set up 'Included Names' worksheet as shown below.


              Step 4: Create calculated field [Show Excluded]

              WINDOW_COUNT(ATTR([Table Name])) < 2


              Step 5: Set up 'Excluded Names' Worksheet as shown below. Make the [Names Filter] in 'Included Names] worksheet to apply to 'Excluded Names' worksheet.


              Then add both worksheets to a dashboard. Any changes made in the included Names worksheet will update the Excluded Names list.


              Hope this helps.


              • 4. Re: List items excluded from filter
                Gene Kucinkas

                Ossai & Hari both solutions work well for the stated problem, however I failed to state that my dataset is about 6 MM records so duplicating the datasource for a join/union isn't a viable option unfortunately. Going to keep digging on my end, thanks much for the new approaches though.


                - gene