Categories
- All Categories
- Oracle Analytics and AI Learning Hub
- 39 Oracle Analytics and AI Sharing Center
- 20 Oracle Analytics and AI Lounge
- 276 Oracle Analytics and AI News
- 50 Oracle Analytics and AI Videos
- 16.2K Oracle Analytics and AI Forums
- 6.4K Oracle Analytics and AI Idea Labs
- Oracle Analytics and AI User Groups
- 102 Oracle Analytics and AI Trainings
- 17 Oracle Analytics and AI Challenge
- Find Partners
- For Partners
PREVIOUS YEAR MTD CALCULATION
Hi All,
how can i calculate previous year MTD in OBIEE RPD?
I tried below calculation but getting below error.is there any other way to achieve this or am i missing something?
AGO(TODATE(METRIC,MONTH_LEVEL) ,YEAR_LEVEL,1)
ERROR: [nQSError: 22044] Nesting of AGO or TODATE functions with differing level arguments is not supported. (HY000)
Answers
-
If you have a properly defined time dimension you can use ago function. Otherwise u can achieve it with the timestamp functions
sum(case when YOURDATECOLUMN between timestampadd(SQL_TSI_YEAR,-1,timestampadd(SQL_TSI_DAY,-DAYOFMONTH(Date '@{AsOf}{2017-05-05}')+1,Date '@{AsOf}{2017-05-05}')) and timestampadd(SQL_TSI_YEAR,-1,timestampadd(SQL_TSI_MONTH,1,timestampadd(SQL_TSI_DAY,-DAYOFMONTH(Date '@{AsOf}{2017-05-05}'),Date '@{AsOf}{2017-05-05}'))) then YOURMEASURECOLUMN else 0 end)
You can Replace ASOF presentation variable with CURRENT_DATE to get whole month in previous year. If you need MTD you will have to replace the second part with the formula for previous year current date
Basically
Previous Year Current Date
timestampadd(SQL_TSI_YEAR,-1,CURRENT_DATE)
Previous Year Month Start Date
timestampadd(SQL_TSI_YEAR,-1,timestampadd(SQL_TSI_DAY,-DAYOFMONTH(CURRENT_DATE)+1,CURRENT_DATE))
Thanks
0 -
thanks for the reply.
i want do it in the rpd for adhoc reporting.
the above calculation in rpd is wrong?
0 -
Yes it will
0