Categories
- All Categories
- Oracle Analytics and AI Learning Hub
- 44 Oracle Analytics and AI Sharing Center
- 19 Oracle Analytics and AI Lounge
- 278 Oracle Analytics and AI News
- 56 Oracle Analytics and AI Videos
- 16.2K Oracle Analytics and AI Forums
- 6.4K Oracle Analytics and AI Labs
- Oracle Analytics and AI User Groups
- 103 Oracle Analytics and AI Trainings
- 20 Oracle Analytics and AI Challenge
- Find Partners
- For Partners
Configuring an analysis to automatically use the previous month in the date column.
I have an analysis that I want to send out automatically once monthly. Is there a way to configure the analysis to use the previous month's data? I currently have it set up to pull anything greater than or equal to the first of the year, and users will filter out the previous months.
Answers
-
This filter returns dates from the previous month:
"Table"."Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH,-2,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)-1),CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_MONTH,-1,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)),CURRENT_DATE))
0 -
Oops. That one is for two month before the present. Use this one:
"Table"."Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH,-1,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)-1),CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)),CURRENT_DATE)
0 -
OR build a proper Calendar dimension and dispense with the calculations each time you need a variation on it. Calc ONCE use MANY ...
0
