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

Week Ending Date Calculation

Received Response
3
Views
3
Comments
647304
647304 Rank 1 - Community Starter
edited Aug 13, 2024 6:15PM in Oracle Analytics Cloud and Server

Hi Guru's,

I have a requirement to calculate the Weekending date as week data is Saturday to Friday.  Start is Saturday 12:01AM.  End is Friday 12AM

Column A Column B(Should be populate the week Ending Date)

01 July 2016 25 June 2016

02 July 2016 02 July 2016

03 July 2016 02 July 2016

04 July 2016 02 July 2016

05 July 2016 02 July 2016

06 July 2016 02 July 2016

07 July 2016 02 July 2016

08 July2016  02 July 2016

09 July 2016 09 July 2016

10 July 2016 09 July 2016

11 July 2016 09 July 2016

12 July 2016 09 July 2016

13 July 2016 09 July 2016

14 July 2016 09 July 2016

15 July 2016 09 July 2016

16 July 2016 16 July 2016

17 July 2016 16 July 2016

18 July 2016 16 July 2016

19 July 2016 16 July 2016

20 July 2016 16 July 2016

21 July 2016 16 July 2016

22 July 2016 16 July 2016

I know there is post of Week Ending Date Calculation but it is different from my requirement. Please help me to achieve this.

Thanks in advance.

Regards

@li

Answers

  • 647304
    647304 Rank 1 - Community Starter

    I know how to do in TSQL below is the code i can use. But i am not able to translate the Module % in OBIEE. I am getting error when i use % sign.

    weekStartSaturday = dateadd(dd, - datepart(dw,Current_date) % 7, current_date)

    Regards

    @LI

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    Did you try to do it by yourself? Because the post you link give most of the things you will need ...

    Based on your example, assuming you want to calculate value of Column B based on value of Column A you are looking to get the date of the last Saturday before the date (or the date itself if it's a Saturday).

  • Gianni Ceresa
    Gianni Ceresa Mod

    % is the modulo operation, if you look in the formula editor into the math functions area you find the "mod" function doing the same ...

    TIMESTAMPADD(SQL_TSI_DAY, MOD(DAYOFWEEK("Time"."T00 Calendar Date"), 7) * -1,"Time"."T00 Calendar Date")

    DAYOFWEEK return you the day number in the week (1= Sunday, 7 = Saturday).

    If it match what you were looking for close the thread as it will help others knowing they can find something useful on here ...