2 Replies Latest reply on Jan 6, 2017 9:22 AM by Joe Oppelt

    Sheet Selection based on which filter is being used

    Amy Hamilton

      Hey group! I would like to use the Sheet Selector idea in a more complex way... I know we can create a parameter or action filter to select which sheet to view. However I already have filters I would like to use, and I'd like the sheet being viewed to change depending on which filter I'm using to make a selection. I have 3 different filters: Level 1, Level 2 and Level 3. Each of these contains a list of departments and each level breaks down the department into smaller teams - Level 1 broken down into various Level 2s, which is broken down into more Level 3s.


      When viewing at Level 1 I would like them to see a sheet with a higher level of aggregation (for example sales by country).

      When viewing at Level 2 I would like them to see a different sheet with a lower level of aggregation (example sales by Region)

      When viewing at Level 3 I would like them to see a different sheet with again a lower level (example sales by city).


      So in essence, I would like to create a way to do the following: If X filter is chosen, use Y sheet.

        • 1. Re: Sheet Selection based on which filter is being used
          Wim Kegels

          Hi Amy,

           

          this is kind of hard to answer without your data, but couldn't you create a Boolean calculated field that checks whether your filter is in use? Something like

           

          IIF([filter1]=<some value that indicates filter1 is active>, true, false).  Include this on the sheets you want to show (true) or hide (false).

           

           

          Hope this helps.  If it doesn't, can you add a packaged workbook with some data?

           

          Kind regards,

           

          Wim

          • 2. Re: Sheet Selection based on which filter is being used
            Joe Oppelt

            Wim is on to something there.

             

            You can create a calc like this:

             

            {  FIXED : COUNTD([whatever field is used for filter 1]) }

             

            This will give you the count of all values for filter 1.

             

            Then you can compare that value to COUNTD([field 1]) on the sheet, and if they are the same, then the user didn't filter out anything from field 1, and therefore is not using the field 1 filter.

             

            Do the same for the other filters.

             

            Find the one that has stuff filtered out.

             

            Base your sheet selector condition on the result of these calcs.

             

            Question:  What stops the user from filtering on TWO or THREE of the filters?