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

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
486
Views
4
Comments
3173736
3173736 Rank 2 - Community Beginner
edited Aug 13, 2024 7:08PM in Oracle Analytics Cloud and Server

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,

Answers

  • Frog Toad
    Frog Toad Rank 4 - Community Specialist

    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

    1 First analysis.jpg

    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

    1 filters.jpg

    Final view analysis :

    2 analysis.jpg

    Result:

    3 result.jpg

  • 3173736
    3173736 Rank 2 - Community Beginner

    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
    Mark.Thompson Rank 6 - Analytics Lead

    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
    3173736 Rank 2 - Community Beginner

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