1 Reply Latest reply on Feb 7, 2018 8:10 AM by Jack Watts

    Filtering on a field that isn't in view

    Jack Watts

      Hi All,

       

      I've got a rather complex workbook. My dashboard is made up of a map, bar/bullet graphs, and a pane across the top where I'll be providing summary values like averages, number of cities selected, etc.

       

      Users select a city they want to compare with. This feeds into a calculation that basically sets the selected city as 1, and all else as 2.

       

      I'm not using any quick filters - everything is parameter based because there are several filters I'm offering such as "same county", "same region", "within 25 miles", "within 50 miles", etc. where I have feeder calculations that compare each city's value to the value of selected city, then the final calculation toggles between the filters based on which "filter choice" is selected in a parameter (the following calculation is on the filter shelf of the map, bar, and bullet graphs, showing all "True" values).

       

      case [Show Cities:]

      when "Less than 100 mi" then [Less than 100 mi]

      when "Less than 25 mi" then [Less than 25 mi]

      when "Less than 50 mi" then [Less than 50 mi]

      when "Same County" then [Same County]

      when "Same Region" then [Same Region]

      when "All" then TRUE

      END

       

      This works fine, so long as the "city" granularity is in the view as in the bar and bullet graphs, but for the summary values where I'd like to show based on the cities that are in the other graphs, I can't place city on the detail shelf because it will disaggregate the measures, and there's no "city filter" I can apply to the sheet as users are not selecting actual cities. Because the calculations underlying these are table calculations

       

      (Results are computed along City, sorted ascending by Min. Selected City)

      LOOKUP(MIN([Primary County]), FIRST()) == Attr(Primary County])

       

      They require to "compute using" the city field, which isn't in the view for the summary data sheet.

       

      Is there any way to hack this using hidden sheets, or is there something similar I'm missing? My initial idea was to create table calculations for all of the values that I want (total(average(value)), window_sum(countd(city)), etc) so the same value appears for each city, then filter to top 1 and hide the rows, but that won't work. I can try to dummy up some data to attach a workbook, but wanted to see if anyone else has any ideas, as it will take some time to recreate each calculation.

       

      Best,

      Jack

        • 1. Re: Filtering on a field that isn't in view
          Jack Watts

          For anyone who wants to know, I solved the problem of showing aggregate values and incorrect filtering with a couple of calculations. Another issue with what was happening above, is that the using the calculated field of "if attr(city)=selected city then 1 else 2 end" on the color shelf caused the filters to incorrectly filter the cities across worksheets.

          To get only the aggregate values that were shown required calculations such as

           

          WINDOW_sum(

          IIF([Show Cities Filter], COUNTD([City]), NULL))

           

          to get the number of cities shown. From there creating a calculation to color the cities that returned string values rather than numbers

           

          (Results are computed along City, sorted ascending by Min. Selected City)

          if ATTR([city]) = LOOKUP(MIN([city]), FIRST()) then "Orange"

          else "Blue"

          end

           

          and placing that on the color shelf fixed basically all of the issues I was having. To complete the aggregate measures, I placed the orange/blue calc on the rows shelf with measure values on the columns, and then hid all rows that were "Blue", unshow all headers, and everything should be good to go. The window calcs are all based on the hidden values in the view, and because only the "Orange" city is showing, it will never be filtered out. If anyone has any questions (I'm sure it's unclear, and I'll post a packaged workbook whenever it's published)