3 Replies Latest reply on May 10, 2013 11:23 AM by Shawn Wallwork

# How can I filter on a sum calculated for a time span by unique ID?

In the sample I've posted here, I have count(distinct) of [ID] on the Text shelf. ('Distinct' because my source data includes multiple transactions per customer ID.) I would like to identify or filter on whether a customer's total purchases in any given fiscal year are \$1,000 or more. That's the challenge ... An ID might be a \$1K customer in one fiscal year, but not in another.

The first tab is the unfiltered customer counts, and the second tab is just a duplicate with a different title.

To this point, I have been exporting a table of IDs with their sum of sales by fiscal year, and then using that table as a second (aggregated) data source. But I'm tired of that and I want to have Tableau do the work.

I have investigated all sorts of possibilities, but can't figure out how to make it work.

Kevin

• ###### 1. Re: How can I filter on a sum calculated for a time span by unique ID?

All you need is a conditional filter on ID where Sales is >= 1000 (see attached).

--Shawn

Edit: If I understood your question/need.

• ###### 2. Re: How can I filter on a sum calculated for a time span by unique ID?

When I posted this question, the "More Like This" section to the left identified a thread that turned out to be directly related to my problem. "Summing across dimensions" - http://community.tableau.com/message/193979#193979

I've worked through this example and it does seem to have answered my question. It's a bit subtle and I'm not sure I totally understand it, but it worked for my sample.

• ###### 3. Re: How can I filter on a sum calculated for a time span by unique ID?

No, I wish it were that simple. I would need the filter to sum Sales within the fiscal year. If sum of Sales within a given year is >999, then keep the ID, otherwise exclude. Your condition sums Sales for ID across all years.