1 yr prior data

[coghost]

Hi,

I have a list report with PO Date col. The purpose is, the user should select a date.Based on this selected date all data for PO Date between (1 yr prior to date selected by user )and (date selected by user shud be displayed).
Eg- if the date selected by user is 20071024(yyyymmdd) , then data for PO Date between 20061024 and 20071024 shud be displayed.
Try1- i have a value propmt with parameter p_date on PO Date. I have added a data item with calc in expression editor as  _add_years((cast(?p_date?),date),-1) .
If I select date as 20070124, it is displaying data where PO Date is 20060124 only.
TRY2-I also tried adding filter on PO Date as PO DATE between_add_years((cast(?p_date?),date),-1) and ?(p_date?).But report is not getting validated..
How do i get all 12 months prior data according to the date selected by the user?

[jethro89]

I would substring the date field and subtract from the year, and do a to_date on it (if you're in oracle). to_date(concat(to_char(to_number(substr(?p_date?,1,4)-1),'YYYY'),substr(?p_date?,5,4)),'YYYYMMDD')

Might be convoluted, but something like this worked for us a while back. We changed our fact table a bit to make it more eligant.

[almeids]

This filter...

[gosales_goretailers].[Orders].[Order date] between _add_years(?pdate?,-1) and ?pdate?

...works fine with the GOSAR data, so the issue is probably with the formatting/casting of your value prompt parameter.  Using a date prompt and eliminating the cast should help if that's an option.  If not, manipulate your string value and get the database to do the work as jethro89 suggests.