In this post we provide, a set of date functions to calculate Year-to-date, Month-to-date, Quarter-to-date filters of any selected year, using only TIMESTAMPADD function.
OTBI does not support TODATE, AGO, ROLL (OBIEE) functions.
similar functionality is obtained by writing complex TIMESTAMPADD functions.some of useful snippets are enclosed below:
- Year – to – Date
YTD (Year to Date) filters based on calendar date:
“Time”.”Calendar Date” between TIMESTAMPADD( SQL_TSI_YEAR , CAST ((@{PV_YEAR}{2021}- YEAR(CURRENT_DATE)) AS INT), TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) and TIMESTAMPADD( SQL_TSI_YEAR , CAST ((@{PV_YEAR}{2021}- YEAR(CURRENT_DATE)) AS INT), CURRENT_DATE)
For Prior Year YTD:
FILTER(“Fact”.”Sales $ USD” USING (“Sales Date”.”Date” between TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM @{P_DATE}{CURRENT_DATE}) * -(1) + 1, @{P_DATE}{CURRENT_DATE})) and TIMESTAMPADD( SQL_TSI_YEAR , -1, @{P_DATE}{CURRENT_DATE})))