6 Replies Latest reply on Feb 2, 2012 10:21 AM by David Drees

Determining if all values are the same

David Drees Jan 30, 2012 1:00 PM

I have a table of text values. For example, I have columns listing products and rows listing US states. In the cells, the data display a text that is either "Active" or "Inactive" indicating whether or not we currently offer that product in that state. What I want to do is view only those rows that have some products Active and some products Inactive. If all cells on the row are "Active" or all "Inactive" then, I don't want to see those rows.

I've tried several different table calculations, but nothing seems to work. Any ideas?

In the sample I've attached, I would want to see all rows except the row for CA and the row for GA.

hide_rows_with_same_data.twbx 32.4 KB

1. Re: Determining if all values are the same

Jonathan Drummey Jan 31, 2012 12:41 PM (in response to David Drees)
I created a table calc that returns a value of 0 when all products are Inactive, 1 when all products are Active, and an inbetween number for other values, then a second Show/Hide Text calculated field that returns "Hide" for values of 0 or 1 and "Show" for everything else. Putting that on the the Filter shelf allows you to filter. Tableau is a little picky about returning numbers from table calcs, which is why this required two calculations. See attached for details.

hide_rows_with_same_data_jtd.twbx.zip 51.2 KB
Like Show 0 Likes(0)

Actions
2. Re: Determining if all values are the same

Jonathan Drummey Jan 31, 2012 12:42 PM (in response to Jonathan Drummey)

You'll need to uncompress the .zip file, apparently the new forums are automatically compressing .twbx files?
Like Show 0 Likes(0)

Actions
3. Re: Determining if all values are the same

David Drees Feb 2, 2012 7:20 AM (in response to Jonathan Drummey)

Jonathan,

Thanks for your reply. I think that is getting me closer. However, while your solution allows me to filter the appropriate rows in theory, when I put the 'Status' dimension in the Text section, it seems to throw the table calculation off. I need to be able to see the results in the table while also filtering the rows. It seems that I can get one or the other, but not both.

Using the workbook that you sent, if you try to drop "Status" on the Text box, you will see what I mean - suddenly, there is overlapping text.

Am I missing something?
Like Show 0 Likes(0)

Actions
4. Re: Determining if all values are the same

Jonathan Drummey Feb 2, 2012 8:28 AM (in response to David Drees)
When you add Status to the view, you are adding another dimension to the query that the table calcs are performing, so more marks are returned and you get overlapping values. However (and this is a good however), the way the data is arranged there is only one Status for each State/Product combination. So putting a calculated field with ATTR([Status]) on the Text shelf lets you get the right colors and lets the filter happen.

Jonathan

hide_rows_with_same_data_jtd 2.twbx.zip 82.0 KB
Like Show 0 Likes(0)

Actions
5. Re: Determining if all values are the same

Shawn Wallwork Feb 2, 2012 8:40 AM (in response to Jonathan Drummey)

To not much effect. Most of the ones I'm seeing are the same size as the original file (which is already a zip file.)
Like Show 0 Likes(0)

Actions
6. Re: Determining if all values are the same

David Drees Feb 2, 2012 10:21 AM (in response to Jonathan Drummey)

That's it! Now, I just need to understand why that works. I'm still trying to get a handle on table calculations. Thanks so much for your help.
Like Show 0 Likes(0)

Actions