3 Replies Latest reply on May 28, 2012 7:43 AM by Jonathan Drummey

    Filtering aggregated data

    Hari Venkat
    Hari Venkat May 25, 2012 2:02 PM

      I'm not sure if this has come up before (it probably has) but I couldn't find anything.

       

      Let's say we have a situation like this (representation of a window in tableau) :-

       

      Year | Cars | Count(Cars)

      2001 | X      | 20

              | Y      | 10

              | Z      | 70

      -----------------------------------------

      2002 | X      | 30

              | Y      | 50

              | Z      |  30

       

      If it's required to display only the highest count in each pane, how would the "Cars" be filtered? In this case, it should just show Z for 2001 and Y for 2002. However, trying to filter it by Top 1 Count(cars) filters by the entire dimension (both years included) so it returns:-

       

      2001        | Z      |  70

      2002        | Z      |  30

       

      When it should actually return 2002 | Y | 50, since Y was the highest in that year. Any way to solve this?

      • 115 Views
      • Categories: Calculations
      • Tags:
        • 1. Re: Filtering aggregated data
          Jonathan Drummey
          Jonathan Drummey Ambassador May 26, 2012 4:03 AM (in response to Hari Venkat)
          Hello,
          Attached is one solution. I used a calculated field called Count Car Field:
          IF COUNT([Car #]) = WINDOW_MAX(COUNT([Car #])) THEN 1 ELSE 0 END
          Drag that field onto the  Filter Shelf, and set the filtered value to 1.
          By default, the Compute Using for this field in the view is set to Table (Down), so it will compute only one maximum for the whole table. To get it to partition on Year, set the Compute Using to to Car, so the calculation will be computed for each car and restart for every Year.
          Cheers,
          Jonathan
          1 of 1 people found this helpful
          • 2. Re: Filtering aggregated data
            Hari Venkat
            Hari Venkat May 26, 2012 8:12 AM (in response to Jonathan Drummey)

            Thanks for the assist!

             

            I figured out a similar way this morning, but your method should be useful for other similar situations.

             

            Basically, I used a table calculation on CNT(Cars) and chose the Pane (down) option. I used a custom formula for the table calculation (which is pretty much the same as the one you wrote up) setting all non-max values to null, then filtered out the nulls.

            1 of 1 people found this helpful
            • 3. Re: Filtering aggregated data
              Jonathan Drummey
              Jonathan Drummey Ambassador May 28, 2012 7:43 AM (in response to Hari Venkat)

              Hi Hari,

               

              Like many situations in Tableau, there are multiple ways to reach your goal.