1

I need to create a report in salesforce for the custom object from a particular date field. I do not want to do it from filter criteria "Next 90 days".

The scenario goes this way:

I have a Subscription date on Contract custom object and i need to create a report for "Subscription End Date" following 3 months i.e next month, next to next month and again next month. For this i created a formula field which i will pull into reports.The formula that i wrote worked till November and then failed. Can any body help me modify it so that my report can be created.

Any other help is appreciated.

IF(
 (OR(
   (YEAR(Subscription_End_Date__c) = YEAR(TODAY()) && MONTH(Subscription_End_Date__c) = MONTH(TODAY()) +1),
   (YEAR(Subscription_End_Date__c) = YEAR(TODAY()) && MONTH(Subscription_End_Date__c) = MONTH(TODAY()) +2),
   (YEAR(Subscription_End_Date__c) = YEAR(TODAY()) && MONTH(Subscription_End_Date__c) = MONTH(TODAY()) +3)
  )
 ),"True","False"
)
  • Why not use the ootb filter criteria? – PepeFloyd May 13 '15 at 6:44
  • the formula compares the year and the next 3 months in the same year.. so for December in current year & months in next year won't work – Vamsi Krishna May 13 '15 at 6:45
  • @VamsiKrishna just edited my answer in anticipation of a potential issue with my first suggestion. If I'm missing anything, please do let me know. – Alex S Jun 18 '15 at 20:19
0

You can use the Relative Date Filter NEXT 3 MONTHS

compared to Next n Days, Next n Months doesn't include days in current month. it starts from 1st day of the next month and runs until last day of the nth month.

here's the actual description of this filter

NEXT n MONTHS

Starts at 12:00:00 a.m. on the first day of the month after the current month and continues until the end of the nth month.

check this salesforce help article for complete details

Understanding Relative Date Values for Filter Criteria


EDIT

based on your comments, I believe you can still achieve this using relative date filters.

check this screen shot.. I have used standard contract report with standard contract end date, but you can change it to your custom object & custom date field..

enter image description here

| improve this answer | |
  • I earlier mentioned that i do not want to use relative filter. – guest610 May 13 '15 at 6:31
  • y can't you use the relative filters ? – Vamsi Krishna May 13 '15 at 6:37
  • I cant use relative filter cuz the day calculation wont be exact as i am going to schedule the reports. – guest610 May 13 '15 at 7:11
  • so u want to include the dates from Today until the last day of the 3rd month ? – Vamsi Krishna May 13 '15 at 7:25
  • 1
    I have updated my answer to cover your scenario. see if it works for you. – Vamsi Krishna May 13 '15 at 7:51
0

In order to make sure that your report includes subscription end dates from next year, while you're in November / December (& October?), you need to include

OR((YEAR(Subscription_End_Date__c) = YEAR(TODAY()), (YEAR(Subscription_End_Date__c) = YEAR(TODAY()) + 1)

for each line.

To avoid picking up records from November / December 2016, just add an AND with Subscription_End_Date__c < TODAY() + 120 days.

I won't post the whole formula because I won't get the syntax right..

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.