Setting relative dates in a filter for an OTBI Analysis

Tim Welsh

Summary

In an OTBI Analysis is there a way to add into a relative date range into a filter?

Content

I need an Analysis with a filter to pick up dates within the last week.  I don't see a way to do this in the normal filter properties and I'm getting errors when i add the below code into "Convert this filter to SQL"  Is there a way to do this either with the regular filter or with the Advanced SQL filter?  

 BETWEEN (SYSDATE - 7) AND SYSDATE

Manoj Cheruvathoor-17353

Hi Tim,

 

you can look at the TIMESTAMPADD function. use a negative number (I think it should be -7 in your case) and the sysdate. use this in the in the filter.

Thanks
Manoj

Manoj Cheruvathoor-17353

hi Tim,

I think this should work for you

"date variable" > TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE) 

date variable is the date field that you are filtering for. just put this in the sql filter. Hope this helps

Thanks
Manoj

RichardChan

SYSDATE is CURRENT_DATE in OBIEE for your future reference

Tim Welsh

Thanks Manoj, using a filter on date with the SQL expression of TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)  nailed it.  

Manoj Cheruvathoor-17353

Hi Tim,

glad to hear, it worked for you. instead of the day you can use these variables

SQL_TSI_SECOND,SQL_TSI_MINUTE,SQL_TSI_HOUR,SQL_TSI_DAY,SQL_TSI_WEEK,SQL_TSI_MONTH,SQL_TSI_QUARTER,SQL_TSI_YEAR  for different time periods. You can see worked examples in the OBIEE help https://reporting-bichf06.taleo.net/analytics/olh/l_en/calendar.htm#CHDJDGBE . Hope this helps.

thanks
Manoj

Nicole Hicks

FILTER("Requisition Status - Historical"."Req. Historical Status Start Date" USING (("Requisition Status - Historical"."Req. Historical Status Start Date" IN (TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)))))

I am getting an error on the formula above. Not sure what I am doing wrong.  Can you provide instructions on where and how to enter the formula and direct me to the training on formulas/filters?

 

"date variable" > TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE) 

Tim Welsh

Nicole, what worked for me was to add the string in the SQL Expression field and set Operator as "is greater than"

Nicole Hicks

Thank you Tim, that's what I was doing wrong.  Woohoo!!!!

Tim Welsh

Manoj,

Do you know how I can capture this date (range) for display in the report output header?  

Thanks,

Tim

Manoj Cheruvathoor-17353

hi Tim,

Do you want the period to be displayed as header of the column? or as the header of the report?

thanks
Manoj