Set Renewal Date by Month and Day

We have a couple of fields built-in that could help but not sure if this will help your monthly customers depending on your use case.  

The first is on the CustomerInfo object and called Lifecycle in Weeks that is a formula 

If Original Contract Date (OCD) is less than today
Formula :ABS(CEILING((JBCXM__OriginalContractDate__c - TODAY())/7))
Example OCD = 06/23/2015, Today = 11/16/2015Lifecycle in Weeks = (06/23/2015-11/16/2015)/7, Lifecycle in Weeks = 21

We also have a field on CustomerInfo called Lifetime in Months that increments each month.

Formula :(Year( TODAY() ) * 12 + Month( TODAY() ))-(Year( JBCXM__OriginalContractDate__c ) * 12 + Month(JBCXM__OriginalContractDate__c)))
Example OCD = 12/3/2013, Today = 11/16/2015
Lifetime in M((2015*12)+11) - ((2013*12)+12), Lifetime in M23

What you will need - Original Contract Date (OCD) - the date that they became a customer

This works for most reports. But when we want to create a create to show upcoming renewal for the quarter, you cannot add "Lifetime in Months" or "Lifecycle weeks" to the By statement in a report.

Formula fields do not allow the ability to group in the by section.  One thing you can do is write the value of the formula field to a custom number field on the Customer Info object via a rule.  This would allow you to group by this field.  Let me know if you want to discuss on our Friday call.

We were able to extract the date and get this to work. However, because we had to create a new text field for the number, we cannot sort correctly. The numbers in graphs and reports sort (1, 10, 11, 12 instead of 1, 2, 3, 4, 5).

How can we get this to sort numerically? If we create a new field and populate it as a number instead of a text, the sort by won't allow us to add the field.

Hi Lane,

Grouping by MONTH should be fairly straightforward, since we have some in-report summarization options. If you pull OCD directly into the "By" section, you should see these like so:

Would these options suffice, or do you really need to also group by DAY? If you do, we could modify the formula that extracts this from OCD such that 1, for example, gets stored as "01" when converted to text (just one potential solution to get the sorting right).

Thanks,
Manu

Instead of using a text field to store these values, using a number field will help with the sorting. Number fields and text field sorting work differently. 

@Manu, Unfortunately, we do need this by the specific day.

@Sidhu, When I create a formula field and a number field, then use a rule to push the formula result to the number field via a rule, Reports 2.0 does not let me Group by the field.

Is there a suggestion on how to make this work since our instance is not allowing us to group by the number field?

Hi Lane,

I just updated the two fields you created to reflect my previous recommendation. I think it's working as expected. Could you please check once on your end by building a report with the second field in the "by" section and then sorting?

Thanks,
Manu

Hi Manu,

Thank you for making the edit to write it as "01" instead of "1".  This appears to be working now.

Thank you