Sum of count if "new"

tableaucommunity@tableau.com — Fri, 20 Jun 2014 17:48:46 GMT

I'm attempting to get a total vs. new count of e-mail addresses, where "new" means "e-mail has not been on the list for the past year".

I have a data source table with dates and e-mail addresses. I

The dates are actually one per week, so I tried making a WINDOW_COUNT calculated field, which works only by e-mail address:

IF COUNT([Email])=1 THEN

IF WINDOW_COUNT(COUNT([Email]),-51,-1)=0

THEN 1

END

That's my function - basically, if there's a value for this e-mail for the current column, and it doesn't appear in the previous 51 columns, then it should count as 1.

This works, but it gives me either 1 or 0, depending on if the specific e-mail address is new that week. My Columns has DAY(date_field), Rows has email_field, and Marks has the calculated field above.

What I really want is a sum - for each date, give me the total # of new e-mails. I know how to do the query I want in SQL, but I'm not quite there with Tableau. If it helps, the query is:

select count(*)

from (select email,c_date from contributor_active

where c_date between $DATE - INTERVAL 1 YEAR and $DATE

group by email

having count(email) = 1 and c_date = $DATE) AS tbl;

I could do this SQL query in my data source, but then I'd have to have 2 data sources if I wanted to show total vs. new on the same graph.

Content in Tableau Community Forums

Sum of count if "new"