Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 77 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations 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