Current week of the year — Oracle Analytics

Oracle Analytics Cloud and Server

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

Current week of the year

Received Response
453
Views
7
Comments
3174977
3174977 Rank 5 - Community Champion

Hi All,

I want to know the logic how to get the current week of the year (June 26th - July 2nd). I don't want week number but I wanted to dynamically display the current week data .

Thanks !

Answers

  • Frog Toad
    Frog Toad Rank 4 - Community Specialist

    Hi!

    SELECT
      TIMESTAMPADD(
           SQL_TSI_DAY
           , 1
           , TIMESTAMPADD(SQL_TSI_DAY
                , DAYOFWEEK(CURRENT_DATE) * -1
                , CURRENT_DATE
           )
       )
      , TIMESTAMPADD(
           SQL_TSI_DAY
           , 7
           , TIMESTAMPADD(SQL_TSI_DAY
                , DAYOFWEEK(CURRENT_DATE) * -1
                , CURRENT_DATE
           )
       )
    FROM [YOUR_SUBJECT_AREA]
    
    
    
    
  • 3174977
    3174977 Rank 5 - Community Champion

    Hi,

    From subject area means ? What actually do i need to give here. I am planning to create a dynamic variable based on this SQL. Can I do that ?

    It is saying TIMESTAMPADD invalid identifier

    Thanks !

  • Frog Toad
    Frog Toad Rank 4 - Community Specialist

    It was sql-query for OBI.

    If you want make dynamic variable, you need use classic sql:


    ALTER SESSION SET NLS_TERRITORY = AMERICA;
    
    SELECT TRUNC(SYSDATE) - TO_NUMBER(TO_CHAR(SYSDATE,'D'))
        , TRUNC(SYSDATE) - TO_NUMBER(TO_CHAR(SYSDATE,'D')) + 7
    FROM DUAL;
    
    

    This 'D'-element returns the number of the day of the week (1-7). The day of the week that is numbered 1 is specified implicitly by the initialization parameter NLS_TERRITORY.

    The best solution, not to depend on regional settings - you need make table "Calendar", where on each date you can assign day number of week (and week number, and year, and a lot more)

    After that, you can get dates using simple query, example

    SELECT DATE
    FROM CALENDAR
    WHERE YEAR = 2016
        AND WEEK_NUM = 24
        AND DAY_OF_WEEK_NUM IN (1, 7)
    
    
  • 3174977
    3174977 Rank 5 - Community Champion

    Hi,

    I used the same but it is just giving only 2 days instead of the whole week

    CA1.JPG

    But with this filters will this change dynamically everytime ? because whenever the user logins they wanted to see current week and further data only

    Thanks !

  • Frog Toad
    Frog Toad Rank 4 - Community Specialist

    You have table "Calendar", good.

    But the field CALENDAR_DAY_of_the_week filled not as you need

    Now you need to fill in the fields of the table so that 26.06.2016 was the first day of week, 02.07.2016 was the seventh day of week (most likely need to use a new column)

    This query is dinamical:

    SELECT DATE
    FROM CALENDAR
    WHERE YEAR =  EXTRACT (YEAR FROM SYSDATE)
        AND WEEK_NUM = TO_NUMBER(TO_CHAR(SYSDATE, 'WW'))
        AND DAY_OF_WEEK_NUM IN (1, 7)
    
  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    Put the textual representation of the week in your calendar dimension for each day ... a well-formed calendar dim will serve you well time and time again.

  • deeps
    deeps Rank 3 - Community Apprentice

    You can use Connect By Level to display all the days within current week. you need to find the current week from your Day table in warehouse.

    refer these:

    List of days between two dates

    https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:14582643282111