topic Re: week over week? in Data & Schema Discussions

week over week?

Won2RoolThemAll — Mon, 16 May 2022 15:18:39 GMT

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.

Re: week over week?

LayeredDelay — Mon, 16 May 2022 23:33:48 GMT

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

Re: week over week?

KailaT — Thu, 19 May 2022 19:03:53 GMT

Hi @Won2RoolThemAll did the explanation above answer your question?

Re: week over week?

Incortaconsulta — Fri, 30 Sep 2022 18:05:33 GMT

Wondering, if all these solutions can be shown by creating 2-minute video snippets for better understanding.

Re: week over week?

KailaT — Fri, 30 Sep 2022 19:44:09 GMT

Great idea @Incortaconsulta. We will look into it!

Re: week over week?

RADSr — Mon, 03 Oct 2022 14:35:20 GMT

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

Re: week over week?

Tristan — Wed, 05 Oct 2022 23:25:18 GMT

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.

Re: week over week?

anurag — Wed, 31 May 2023 21:47:28 GMT

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