5 Replies Latest reply on Mar 21, 2014 5:15 AM by kettan Branched to a new discussion.

    Is it possible to re-use an exclude filter as an include filter in another sheet?

    kettan

      The idea is to show items in a table which are manually excluded from a chart without showing any quick filter.

      Workbook is attached.

      reuse exclude filter as include filter in another sheet.png

      Ps. Dynamic settings of settings  would make this easy (possible?)

        • 1. Re: Is it possible to re-use an exclude filter as an include filter in another sheet?
          Tracy Rodgers

          Hi Johan,

           

          The only way I can think of doing this would be use a parameter. The issue with this though is that you can only choose one item at a time.

           

          I'll have to think more about this one.

           

          -Tracy

          2 of 2 people found this helpful
          • 2. Re: Is it possible to re-use an exclude filter as an include filter in another sheet?
            Willem Botha

            Hi guys

             

            We're dealing with almost exactly the same thing. A parameter would work perfectly except that it doesn't update automatically if new field values are added in the data set. I was wondering if anyone has come up with some other way to do this?

             

            Thanks,
            Willem

            2 of 2 people found this helpful
            • 3. Re: Re: Is it possible to re-use an exclude filter as an include filter in another sheet?
              Jim Wahl

              I wouldn't exactly call the below solution elegant or easy, but it works and perhaps it will generate some additional ideas.

               

              The basic idea is to have two rows per row_id and have the exclude filter only apply to one row. Then it's possible to setup a separate filter that shows all row_ids with a count of 1.

               

              To set this up, I

              1. Duplicated the data using a UNION ALL, but appended a "_" to the category and sub-category fields in the second SELECT. Without this, the filter will filter both rows. The JET database uses & for concatenation. It looks like

              SELECT [Orders$].[City] AS [City],
                [Orders$].[Customer ID] AS [Customer ID],
              ...
                [Orders$].[Product Category] AS [Product Category],
              ...
                [Orders$].[Product Sub-Category] AS [Product Sub-Category],
              ...
                "primary" as [Z-data]  # be sure to add comma at end of previous line
              FROM [Orders$]
              UNION ALL
              SELECT [Orders$].[City] AS [City],
                [Orders$].[Customer ID] AS [Customer ID],
              ...
                [Orders$].[Product Category] & "_" AS [Product Category],
              ...
                [Orders$].[Product Sub-Category] & "_" AS [Product Sub-Category],
              ...
                "duplicate" as [Z-data]  # don't forget the comma at end of prev. line
              FROM [Orders$]
              
              

               

              2a. In the primary scatter plot view, add Z-data to the filter shelf select primary.

              2b. Click on the filter box and select "Only Relevant Values" to hide the "_" fields.

              This worksheet should now look identical to the previous version.

              2c. Right-click on the product sub-category filter and apply it to the "show excluded values" worksheet (or, for this example, I just applied it globally).

               

              3a. Switch to the "show the excluded values" sheet and create a copy of the product sub-category field. Since I'm displaying these values, I'll edit the calculated field to remove the "_". Product Sub-Category (copy):

              IF   RIGHT([Product Sub-Category], 1) == "_" 
              THEN LEFT([Product Sub-Category], LEN([Product Sub-Category])-1)
              ELSE [Product Sub-Category]
              END
              

               

              3b. Add this new field to the rows shelf, left of the original product sub-category field.

               

              3c. Create a new field called Filtered?

              WINDOW_COUNT(ATTR([Product Sub-Category])) == 1
              

               

              Add this to the filter view. Right-click on the pill, select Edit Table Calculation > Advanced. Move both Product Sub-Category (copy) and Product Sub-Category to the right-hand addressing box. Click OK and select Restarting every Product Sub-Category (copy). Now we're counting the number of product sub-categories. For non-filtered values, there will be 2 rows (name and name_ for the primary and duplicated rows), and for filtered values only 1 (the duplicate name_). Click OK and select TRUE.

               

              3d. Click on the product sub-category pill and unselect Show headers to hide this column.

               

              Jim

              2 of 2 people found this helpful
              • 4. Re: Is it possible to re-use an exclude filter as an include filter in another sheet?
                Jonathan Drummey

                If you're willing to use a Filter Action instead of a quick filter, you can get the desired results using the technique outlined in Is there any way to store the excluded values(excluded through action filter) in calculated field?. I also came up with a solution using a self-cross product of the filtering dimension and a data blend, but it's complicated.

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: Is it possible to re-use an exclude filter as an include filter in another sheet?
                  kettan

                  Thanks Jonathan. I haven't tried your solution yet, but after reading the solution you referred to, I am convinced that this is the correct answer.