I need to do some reporting on a week-over-week basis. The built-in "ago" and "toDate" functions only support "month", "quarter" and "year".
Any thoughts on how to do this inside a formula on a business schema?
My last-ditch thoughts:
1) create a date dimension alias table with a join on "week ago". But this just seems janky.
2) SQL Window function as auxiliary table but this is a lot of new rows for little value and no flexibility.
I would recommend that you use the alias table and join to the common date dimension table with a join on "week ago" - it actually is a more robust solution. You could do a SQL view using a LAG function, but it's a little more involved and would be more difficult to change the dimension values you want to use etc. You'll have more freedom with the alias table approach.
Here's an example of the lag function courtesy of @awarrier