4 Replies Latest reply on Jan 24, 2019 10:52 AM by jagz

# Filtering to least negative value if all negative. Show least negative and most positive value if both are present.

I have done my best to provide a mock data set. The workbook is attached and I have provided a screenshot of what it current looks like.

I need to know two things:

1. If any States/Counties have all of their stores underwater (negative revenue), and if so, which store number is the least underwater

2. If any States/Counties have stores that are both underwater and above water, and if so, which store numbers are the least underwater and the most above water. (least negative and most positive revenue)

The real data set I am using has an astronomical amount of lines and we want to be able to see the above request. I can't quite figure it out. Any help or guidance would be greatly appreciated. I am still growing/learning in the field of Tableau equations. If you could please provide a "translation" of the formula needed to solve this I would greatly appreciate it. I am having a hard time finding "translations" of Tableau's syntax to help me wrap my head around how it all works. If that is too much extra work I understand.

Thank you!

• ###### 1. Re: Filtering to least negative value if all negative. Show least negative and most positive value if both are present.

Hi Jagz,

These are yours Stores and Calcs:

and for top and Low

Thanks

Deepak

If it Helps, Pl Mark it Helpful and CORRECT to Close Thread

• ###### 2. Re: Filtering to least negative value if all negative. Show least negative and most positive value if both are present.

Deepak Rai,

Thank you for the response, however, this is not what I am looking for. The first example yields the smallest two values in each state, which I do not want. It's a bit hard to explain, I will try to clarify better:

1. If any County has ALL of their stores with negative revenue, I want to know which store has the LEAST negative revenue. I do not want to see any counties that have stores with both positive and negative revenue in this view.

2. If any County has stores with both positive and negative views, show the stores with the least negative and most positive revenue in those counties.

Hopefully this helps clarify. Also, if you could help explain your formulas I would greatly appreciate it. I get confused with how to translate the syntax sometimes and am still learning. I can see what your filters yield and know that it isn't what I want, but I can't fully wrap my head around how that interacts with the data to present that view.

Thank you so much for the assistance.

• ###### 3. Re: Filtering to least negative value if all negative. Show least negative and most positive value if both are present.

I dont think you have all Negative in any County

• ###### 4. Re: Filtering to least negative value if all negative. Show least negative and most positive value if both are present.

Deepak Rai,

My apologies. Please see the attached workbook.