4 Replies Latest reply on Apr 10, 2012 3:50 PM by Ryan Shirley

# Quickly filtering out a middle set of values, leaving the outside ranges.

Ok, so I created a calculated field which I simply called 'RtlWSRatio'. This is a ratio which calculates the sum of retail and divides by the sum of wholesale. The thought being that if the ratio is below 1 then the dealer retailed less product than we wholesaled to them (meaning they have abundant stock). If it is above 1 then the dealer retailed more product than we wholesaled to them and they theoretically have room for more inventory. However, the ratio of 1 (or very close to it) means that they should be at their theoretical status quo, so we want to filter this range out so only the "trouble" dealers get the attention. So what we want to do is be able to filter out the range of 0.9 to 1.1. I was hoping that in the Quick Filter I could simply switch the slider parameter from "Show the following values" to "Do NOT show the following values" but this is not available. What is the best way to accomplish this? Thank you!

• ###### 1. Re: Quickly filtering out a middle set of values, leaving the outside ranges.

Hi Ryan,

You can do this by creating another calculated field similar to the following:

if [RtlWSRatio]>=.9 and RtlWSRatio<=1.1 then 'Hide' else 'Show' end

Then, place this on the filter shelf. Hope this helps!

-Tracy

• ###### 2. Re: Quickly filtering out a middle set of values, leaving the outside ranges.

This sounds like what I am after...however, when I do this I am not able to add this new field to the Filter Shelf. I thought maybe it was because I was using a map to view where these "trouble" dealers were, but even on a blank new sheet I can't add that field to the filter shelf.

On a side note I AM able to add it to the columns shelf and then I can just hide teh "Hide" column, but that seems kind of like a hokie work around.

• ###### 3. Re: Quickly filtering out a middle set of values, leaving the outside ranges.

I had a feeling you might not be able to do this, but I wasn't sure if your calculation RtlWSRatio was aggregated or not. Since it is, Tableau cannot filter on aggregated strings. There is a way around this however, by changing the calculation to look like the following:

if [RtlWSRatio]>=.9 and RtlWSRatio<=1.1 then 1 else 0 end

Place this on the Level of Detail shelf. Then, right click on it and select Continuous. Right click on it again and select Filter. Finally, filter it to 1 if you don't want to show it.

-Tracy

• ###### 4. Re: Quickly filtering out a middle set of values, leaving the outside ranges.

That works well enough. Thank you very much for the help!