Oracle Analytics Cloud and Server

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

Week Start Date (Date return) of week and First & Last Date of last week

621
Views
2
Comments
Rank 3 - Community Apprentice

Respected Members,

Can someone please help me with below Query.

I want to find out

1- Week start date of any date I pass.

Input: 21-April-2021

Result: 18-April-2021

2- Last Week start date and end date of any date I pass.

Input: 21-April-2021

Result: 11-April-2021, 17-April-2021

Please help, TIA.

Tagged:

Comments

  • The ideal solution is to have a proper time dimension containing your own business rules for dates, because start and end date of a week is a something which must follow your own rules and is going to be different for other companies.

    If you want to do that in OBIEE itself you can calculate things by using the date functions available: there is one returning the day of the week of a given date, take your business rules defining what is the start and end of a week and calculate it by using the TIMESTAMPADD function to add or subtract a given number of days from a date.

    It's simple logic based on the day of the week most of the time.

  • Rank 3 - Community Apprentice

    Thank you Gianni for your kind response, I totally understand your point. Although I have found the solution to both question posting below for other fellow member.

    1-

    TIMESTAMPADD(SQL_TSI_DAY,- DAYOFWEEK(MIN(“Dim – Date”.” Date Column”)) + 1, MIN(“Dim – Date”.” Date Column”))

    2-

    "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)

Welcome!

It looks like you're new here. Sign in or register to get started.