+ Start a Discussion
Andy JonesAndy Jones 

Creating a search for Today +/- 7 days

Hi,

What is the formula to create a report that searchs for a date field + OR - a given number of days (7) in thiscase.

I know I could do this from two reports but that seems incredilbly clumsy to me.

Thanks in advance.
Anil KamisettyAnil Kamisetty

Hi Andy

I believe this is available on the Salesfore Report and should be an easy one.

When you customize the report, you will see Filters / Show / Date Field. In the Date field filter, select the Range NEXT 7 Days or Last 7 Days and the report does what you want.
 

Are you looking for this only ? If not, update more details.


Note : If this answers your question, please mark this as Answered, thus helping others.

Andy JonesAndy Jones
Hi Anil, Thanks for the reply, I think you've misunderstood my question however. I'm looking for one report showing all dates + or - (x no.) days. The way you've suggested if I'm not mistaken would only work for the system default 7 or 30 days and I'd have to create two reports, one for previous 7 and one for next 7. I'm looking to consolidate the two reports and might wish to for example view the next 14days and previous 7. Can this be done? Kind Regards Andy Jones Office Manager M: +44 (0) 7920 080 040 T: +44 (0) 8455 618 884 T: +44 (0) 1279 647 039 F: +44 (0) 1279 800 006 www.pulsemarketing.com www.homecinemagallery.com Follow us on Twitter / Facebook /Instagram Pulse Marketing, Unit 4, M11 Business Link, Parsonage Farm, Forest Hall Road, Stansted, Essex, CM24 8GF Sent from Samsung Mobile -------- Original message -------- From: Salesforce Developer Community Date:01/05/2014 03:55 (GMT+00:00) To: Andy Jones Subject: (Salesforce Developers): New reply to your question. Anil Kumar replied to your question at 7:55 PM on 4/30/2014. Your question: “Hi, What is the formula to create a report that searchs for a date field + OR - a given number of days (7) in thiscase. I know I could do this from two reports but that seems incredilbly clumsy to me. Thanks in advance.” Reply: “ Hi Andy I believe this is available on the Salesfore Report and should be an easy one. When you customize the report, you will see Filters / Show / Date Field. In the Date field filter, select the Range NEXT 7 Days or Last 7 Days and the report does what you want. Are you looking for this only ? If not, update more details. Note : If this answers your question, please mark this as Answered, thus helping others. ” Tip! To respond, either reply to this email or click this link: https://developer.salesforce.com/forums/ForumsMain?id=906F0000000AIqN
Scott McClungScott McClung
Hi Andy,
One solution would be to add two date filters to the report and set their logic to OR
Example:
Filter 1: Close Date equals "Last 14 Days"
Filter 2: Close Date equals "Next 7 Days"
Filter Logic: 1 OR 2

This will include everything with a close date in the 21 day window.
Andy JonesAndy Jones
Perfect thanks, didn’t think of doing it like this, I was trying to use the dropdown tabs which are restrictive.
Anil KamisettyAnil Kamisetty
Hi Andy / Scott

Salesforce does provide the ability to use the relative date field (Last / Next ?? Days) in the report for one filter only and that is standard / Out of box Filter. If one creates a Custom Filter (using ADD Filter option), relatives values cannot be used. Date needs to be hard coded.

Salesforce does allow the use of Relative Filters in CUSTOM VIEW for more than one filter. If you can use Custom View to achieve what you want, you can do that.

If you have created two filters using Relative values, please do attach screenshot of the same. It would be good to know what workaround was used.

Feel free to add on how the situation was addressed.

Note : If this answers your question, please mark this as Answered, thus helping others.
Scott McClungScott McClung
Hi Anil,
The report builder does allow you to use relative date literals in the custom filters.  Here is an example of what I suggested to Andy.  

User-added image
User-added image
Anil KamisettyAnil Kamisetty
Hi Scott

I remember adding literals earlier (on a custom object) to my post, it did not work. I tried on the Opportunity object and it does work now. Are there any limitations on the objects which are supported ? If not, is it supported for all data types on all objects ?
Scott McClungScott McClung
I'm not aware of any unusual limitations to it.  I've used it on custom objects and custom date fields without issue.