Date Question >???

[mstrgrl]

I need to create a filter called "Last Year Today" which should be simple using dynamic dates; however,,,, it has to be the same day of week and week of year (both fields in our warehouse) as today is. I can easily get the day and week by doing a report; however, I don't think I can build a filter from report results??? Any suggestions???

Thanks!!!

 

[mstrgrl]

actually it works in SQL+ by joining a table to itself. I have no idea if that is possible to do within MSTR???!

-----------------------------------------------------------

SELECT a.date_id dateTY
,a.fiscal_day_in_week dayTY
,a.fiscal_week_in_year weekTY
,a.fiscal_year yearTY
,b.date_id dateLY
,b.fiscal_day_in_week dayLY
,b.fiscal_week_in_year weekLY
,b.fiscal_year yearLY
FROM date_dim a
,date_dim b
WHERE a.fiscal_day_in_week=b.fiscal_day_in_week
AND a.fiscal_week_in_month=b.fiscal_week_in_month
AND a.fiscal_month_in_year=b.fiscal_month_in_year
AND SUBSTR(a.date_id,1,4) = TO_CHAR(sysdate,'YYYY')
AND b.fiscal_year = a.fiscal_year-1
ORDER BY a.date_id DESC

Sorry this is so ugly!!! Thanks!!

 

[nlim]

if you can get the "date" field on your report looking like this

date
----
20021103

then you can drag this report into the filter section of your final report. The date in the first report is used as a filter.