Start/End of Last Week regardless of run date during this week

Question

Hi everyone,

I'm new to OBI and wanted to pick your brain on something that driving me insane.

I want to create a report that will run some aggregation for a date range that spans last week (from Sunday through Saturday) regardless of when its run in the week.

So, example. If today is 2016/02/09, then when i run this report anytime this week, it should automatically pull data from 2016/01/31 - 2016/02/06.

Is this even possible? Appreciate any guidance that can be provided.

Thanks,

3173736 · Answer

thank you, this worked like a charm. I appreciate your help

Mark.Thompson · Answer

Try this as a SQL filter:

"Your Times Table"."Your Date Column"

between

timestampadd(SQL_TSI_DAY, -DAYOFWEEK(current_date)-6, CURRENT_DATE)

and

timestampadd(SQL_TSI_DAY, -DAYOFWEEK(current_date), CURRENT_DATE)

3173736 · Answer

Thank you, this is a great start.

But, this gives me a date range that spans 7 days ago through today. What i'm aiming for is to get the "date range that spans last week (from Sunday through Saturday) regardless of when its run in the week."

I think that's where i'm getting confused. So, the pseudo code would be.

WHERE DATE RANGE IS 'SUNDAY OF PREVIOUS WEEK' TO 'SATURDAY OF PREVIOUS WEEK'

Does this help?