2 Replies Latest reply on Dec 17, 2012 7:51 AM by Pietro Sorrentino

# How to filter all my worksheets by a subset calculated in one worksheet

Ok, the title isn't very clear, apologies... nonetheless here is what I'm attempting:

I have data on clients that spend in N different purchases. In one worksheet (worksheet 1) I show on a scatterplot where each dot is a client, how much they spend in TOTAL (i.e. it's a calculated field that sums up all the lines of the different lines for a given client) and how much that spent has grown since last year. I set up a filter by total spend and one by growth rate and pick my subset based on these two filters. Life is good, all works fine.

Now though I would like to know: (a) how many clients I have left (b) how much they spent in total

Even if I do my sum(spend) filter on worksheet 1 a global one, when I do a countd(clients) in a separate worksheet (worksheet 2) I won't get far because clearly my second worksheet will not sum BY client, but rather it will do a sum across all purchases by all clients, so my filter on sum(spend) just doesn't do the trick. So my question is: how do I tell Tableau to countd(clients) only for those clients left in my first worksheet? Or else, does it exist a way to create a dummy variable that is 1 if a client shows on my first worksheet and 0 otherwise? Because at that point it would be very easy, I could simply filter the second worksheet by this dummy variable.

Any input would really be helpful! (I've ben banging my head on this for a while now...)

Thanks!

Pietro

• ###### 1. Re: How to filter all my worksheets by a subset calculated in one worksheet

Could provide some sample data that represents your situation, and a mock-up of the result you expect to see from that data?

• ###### 2. Re: How to filter all my worksheets by a subset calculated in one worksheet

Hello Joe,

Reading back at my post I realized the same thing can be put in a much easier way. Imagine a set of data where you have sales for different companies and you want to filter out all companies that have sold less than a certain \$ amount. You do that, but then you would like to know: (a) how many comanies you have left (b) how much they spent collectively the companies left.

Basically, you can easily show this on a table and the gran totals will be exactly the numbers I'm looking for. But how to show just the gran totals on the dasboard (and not the rest of the table)?

Thank you

Pietro

(not sure how to upload an example file...)