Last7Days formula

[shannonlp]

I have a report where I need to have a summary based on the last 7 days. I would normally do something like

If {orders.order.date} in Last7Days Then
{orders.order.amount}
Else
0
My understanding is that this works based off the current date, regardless of what values are in the order.date field. I need the report to show the last 7 days based on the most recent date. For example, today is 8/23 but when I run this report the most recent data is from 8/22. I need to show data from 8/16 – 8/22. Is there a way to do this?

Thanks,
Shannon

 

[lbass]

You could use:

{orders.order.date} in currentdate-7 to currentdate-1

-LB

 

[shannonlp]

Thanks for your response. That won’t work since the data is not consistently one day old. When the report is run, the order.date could be the current date, one day ago, two days ago, etc..

 

[lbass]

What version of CR are you using? You should always specify this as suggestions vary based on the version.

-LB

 

[goesnowhere]

Create SQL Expression field called LastKnownDate (or something similar)

Enter something similar

(select max(datefield) from table)

Use %LastKnownDate SQL Express field in the record selection clause

table.date >= {%LastKnownDate) - 7

You may take a performance hit on the speed of the report so try to limit your subset of records in other ways.

David

 

[shannonlp]

CR XI

 

[goesnowhere]

Just in case future dates exist, you would want

(select max(datefield) from table where table.date <= current timestamp)

current timestamp function may differ based on the database platform that you are using

Then in the record selection clause

table.date >= {%LastKnownDate) - 7 and
table.date <= {%LastKnownDate)