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

# Filtering aggregated data

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?

• ###### 1. Re: Filtering aggregated data
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

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

Hi Hari,

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