PREVIOUS YEAR MTD CALCULATION

875299

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)

asim cholas

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

875299

thanks for the reply.

i want do it in the rpd for adhoc reporting.

the above calculation in rpd is  wrong?

asim cholas

Yes it will