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 !
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]
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
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)
I used the same but it is just giving only 2 days instead of the whole week
But with this filters will this change dynamically everytime ? because whenever the user logins they wanted to see current week and further data only
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)
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.
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
Hello Oracle Analytics Community, We are investigating a dashboard compatibility issue between Oracle Analytics Server (OAS) 7.6 and OAS 7.0. Some dashboards originally created in Oracle BI 12c can be opened in OAS 7.6 but fail to open after being archived and imported into OAS 7.0. During our investigation, we found the…
Hi all, We’re running into an issue with an on-premises Oracle Analytics Server 2026 integrated with Active Directory and hoping someone has seen this or knows if a patch is available. Environment: Oracle Analytics Server 2026 (on-premises) WebLogic 14.1.2.0.0, JDK 21, Oracle Linux DB-backed catalog (Oracle DB, BIPLATFORM…
We have a requirement Creating a Bar graph using Cost Center Tree hierarchy as prompt/filter. Created a Bar Graph using Cost Center Tree Hierarchy as a prompt. When I'm selecting values through selection steps from Cost Center Tree Hierarchy, the Bar Graph not refreshing as the selected values are not passing to Graph…
I have 2 tabs on the workbook that use the same dataset. I would rather not use 2 different datasets. Can I code an expression filter or something to remove those records?
Can you please help resolve this error. WebLogic version is 12c. OAS is 6.4