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

    Pietro Sorrentino

      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