Need help with a selection criteria formula

[Former Member]

Let me start by saying that I rarely ever work in CR11. I have a little issue with a selection criteria and I'm not really sure how to tackle it. The scenario is really simple and I suspect the solution is as well but for whatever reason I just can't wrap my head around it.

There's table called saleslines and a table called tasks. Salesline table as named contains all saleslines within a month time. The tasks table contains a list of contacts made by our sales team to the customers for a whole 12 months. Both tables linked by account number field. In my report I'd like to show saleslines that match sales order creation date to any contact date within 60 days before the SO creation date.

I'm not really sure how to write up this selection formula, I'd like to hear your thoughts on this. Thanks in advance!

Two fields in question.   salesline.createddate  and tasks.lastcontactdate

Ps. There could be multiple records in the tasks table that contain the same dates on same account numbers but with different task status, if that matters.

[Former Member · Accepted solution]

Alright guys, I am close but I'm really not sure how to end this formula. I currently use the following formula:

salesline.createddate <= tasks.lastcontactdate   - this is a good formula but it looks at all the dates in the tasks table, I only want it to retrieve the records that have tasks on either createddate or within 60 days before that. How do I do this?

[Former Member · Accepted solution]

You can add or subtract days from a date...  {datefield}-61 will give you a date 61 days in the past.

So if I understand what you want, build a selection formula something like 

{salesline.createddate} > {tasks.lastcontactdate}-61 and

{salesline.createddate} <= {tasks.lastcontactdate}

If I did not get it right, you can build on this.

Debi

[Former Member · Accepted solution]

Thanks for the input Debi.

I've tried all sorts of different ways but finally got it. Here it is:

DateDiff ("d",{Sheet1_.TASK_DATE} ,{SalesOrders_.CREATEDDATE} ) <=60 and

DateDiff ("d",{Sheet1_.TASK_DATE} ,{SalesOrders_.CREATEDDATE} ) >=0