PREVIOUS YEAR MTD CALCULATION

Question

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 · Answer

Yes it will

875299 · Answer

thanks for the reply.

i want do it in the rpd for adhoc reporting.

the above calculation in rpd is  wrong?

asim cholas · Answer

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