week over week?

[Won2RoolThemAll]

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. 

[RADSr]

I have found that creating an index on a calendar table  - I use day index, week index, month index, year index, and "fiscal" versions if applicable - is really helpful.    Want to compare this week to last?  $CurrentWeekIndex -1       

[Tristan]

Actually, in the latest version of Incorta the ago function does support "DAY" as a parameter.  If you want to go back a week, you would pair "DAY" with 7.  Here is an example formula that aggregates revenue for a week ago:

sum(
	SALES.Sales_Date_dim.Revenue, 
	ago(
		SALES.Sales_Date_dim.Sales_Date, 
		7, 
		"DAY"
	)
)

The output looks like this (the 3rd column uses the formula above):

The docs site talks about it here.

[anurag]

This video shows how to do week over week sales in Incorta.