The best advice I can give you is to do the calculation in the underlying database. When you use Tableau's Custom SQL queries, Tableau wraps your query in it's own query, in effect turning your query into a subquery. This is one reason it is so slow. You're better off doing a CREATE VIEW in the database using a GROUP BY to aggregate your date field, and then connecting to this view.
The other option you should consider is to make a straight live connection, build your viz and then do an extract using the aggregation option. (That doesn't have a week option, so that might not work.)
Whatever you do, doing an extract will definitely speed things up.