Dynamic Date Difference

[cable1]

I want to take a prompt answered date, P1, subtract 90 days from P1 to get P2 and end up with a date range of P2 to P1. I have played with the following syntax, but I cannot figure out a way to replace P2 with (P1-90 days) since "90" is not of type "date".

ApplyComparison ("#0 BETWEEN (dateadd(day, -datediff(day, #1, #2), #1)) and #1", Date@ID, ?[P1], ?[P2])

 

[nlim]

I would suggest the following.

1) create a value prompt of the date type. Let say it is called P1.
2) then create a attribute qualification filter for Date. Select ID, and choose greater than operator. Then pick custom, and type in applysimple("#0-90", ?P1). Call it F1.
3) now use F1 in a report.

the sql should be something like this
select....
from...
where date > selecteddate - 90

that should work for the lower limit. For the upper limit, just create another date filter where date@id is less than P1.

 

[cable1]

I understand exactly what you are saying and this should work, but P1 is of type date and 90 is of type int. It will not subtract an int from a date. Is there a way I can use the DayofYear function to turn P1 into an int, subtract the two, and then turn that number back into a date?

Thank you for all your help!

 

[nlim]

i think the database will take care of the date conversion. If not, just ask your dba what the syntax is to convert a date to 90 days before, and use it.

For example if your dba says the syntax is

function('1/31/2004', 1, 90)

then just use applysimple("function('#0',1,90)", ?P1)

 

[cable1]

The system will not subtract an int from a date. The whole problem is trying to figure out how to convert a date to 90 days before the entered date.

I have played around with the DayofYear function in filters and derived metrics, but I still cannot get it to work.

Thanks anyway.

 

[cable1]

I figured it out!

ApplySimple("dateadd(day, -90, #0)", ?P1)