Since this was posted right before Christmas, I'll take the liberty to ask again. Anyone able to help?
Let me just check that I understand the logic you want first. You want to be able to get aggregates (weekly averages in the case of the example) across the database, but excluding any IDs which ever occur more than once in any week from all of the aggregates - i.e. you don't even want to include one instance of the duplicated rows and you never want to include an ID that has ever been duplicated within the aggregates for any week, even weeks where it is not duplicated. Is that right?
I think It would be possible to filter out the rows using table calculations in Tableau - though as you say your real database is very large this may not be practicable. It certainly wouldn't count as "simple". The only way I can see to do it with table calculations would be to bring back weekly values for each ID, including a count of values for that ID in that week. You could then have a calculated field which checked for the WINDOW_MAX() of the count of rows for each ID and do all of your aggregate calculations locally with WINDOW_XXX() functions. But it would get horribly messy.
I think some custom SQL is likely to scale much further and make your workbook much simpler.
You understood me correctly.
As you say, the way to do it is to count weekly numbers of each ID. I can calculate these values in a worksheet in Tableau using a table calculation, but I can't then use these results as a filter for another worksheet, which is the problem..
Looks like I might have to go with custom SQL.