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

obiee 11g Get first day of week as monday based on a specific date

Received Response
413
Views
5
Comments
BenFreez
BenFreez Rank 5 - Community Champion
edited Aug 13, 2024 6:09PM in Oracle Analytics Cloud and Server

Hi all,

I'm looking a solution to deploy in the RPD a way to retrieve from a specific date the First Day of the Week based on this date.

My week is starting from Monday to Sunday

Thanks in advance for your help.

Ben

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    You could do this in the ETL by extending your time dimension. By adding an additional column which links back to the 1st day of the week for each day recorded in your time dimension.

  • Joel
    Joel Rank 8 - Analytics Strategist

    Alternatively, you could implement something like this in the rpd:

    TIMESTAMPADD(SQL_TSI_DAY, CASE WHEN DAYOFWEEK("Time"."T00 Calendar Date") <> 1 THEN -1*(DAYOFWEEK("Time"."T00 Calendar Date")-2) ELSE -6 END, "Time"."T00 Calendar Date")

    This formula assumes that Sunday is the 1st day of the week and Monday is the 2nd day of the week (in bold in formula).

  • BenFreez
    BenFreez Rank 5 - Community Champion

    Thanks a lot for the precious formula.

    In my case my week is starting on Monday instead of Sunday. I need to alter your formula to take care of that fact

    Ben

  • Joel
    Joel Rank 8 - Analytics Strategist

    If that sorts out your issue, please close the thread for others who may have this issue in future.

  • BenFreez
    BenFreez Rank 5 - Community Champion

    Thanks a lot for your help.