Use of Date Variable in Filter to obtain Trailing Twelve Months 1

[hubiej]

Hello:
I am trying to figure out how to create a filter that will use the session date to return results for a trailing twelve months. So, I want my result set to have data that is dated between today and (today - 365 days). If it's easier to create this variable in the repository and expose it on the presentation layer, then let me know that too. I'm expecting that I can do this directly in the web cat, but am willing to take any advice I can get.

 

[sfdogmom]

Hi -

You might try converting your date filter to SQL (select date column to filter on, then click on "Advanced" in lower left corner, then "Convert this filter to SQL"). One of our report developers needed to select orders within the last 7 days & he created a SQL statement in the filter:

"- Asset SAP"."SAP Order Date" >TimestampAdd(SQL_TSI_DAY, -7, Current_Date)

 

[hubiej]

Excellent tip! After I posted my question, I continued to work on building the filter. My client wanted a full trailing twelve months starting with the last closed period. In order to exclude records from the current period, I ended up with a composite filter using the following:

"- Activity Created Date"."Created Julian Month" >= valueof(CURRENT_JULIAN_MONTH) - 13
AND
Created Month is not equal to / is not in CURRENT_MONTH

Thanks for your reply! I have not found too many forums for Siebel Analytics, so I was ecstatic to find your response.