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

3173736

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,

Frog Toad

Hi!

You can do this by means of two analyses, in the first analysis you need make two calculated members, with dates that you need. In second analysis you need use 2 filters-type "is based on results of another analysis" with link to first analysis

More:

1. The first analysis should contain two calculated members - for the dates that you need.

For simple example - analysis that returns period [7 days ago; Current day]:

Functions for calendar see here (this will help you get any dates)

https://docs.oracle.com/cd/E14571_01/bi.1111/e10544/appasql.htm#BIEUG535

Сalculated members:

TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)

CURRENT_DATE

Save this analysis.

2. Create second analysis, you need make 2 filters-type "is based on results of another analysis" with link to first analysis

In this filters you need to choice first analysis as "Saved Analysis" and choise relationship "is greater that or equal to any" for left-value and "is less that or equal to any" for rigth-value

Final view analysis :

Result:

3173736

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?

Mark.Thompson

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

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