Skip to main content
  • Home
  • Ideas
  • LIfecycle in Weeks formula - please validate and prep for either patch soon or May release worst cas
New Idea

LIfecycle in Weeks formula - please validate and prep for either patch soon or May release worst cas

Related products:None
sdrostgainsightcom
We've been holding on updating the managed 'Lifecycle in Weeks' formula field on our Customer Info object until we have a validated formula - we've got a few customers waiting for this field to function as originally intended:  provide a week number, 1-52, based on the anniversary of the Original Contract Date.

I've provided a formula below. I've also set this formula field up in a demo org and tested it - I can pass along the credentials if you want to look.

Basically, the formula works as follows:
1. builds a date in the current year that is the same month and day as the Orig. Contract Date 
2. Subtracts this date from today's date.
3. If the result is positive, then it divides the different by 7 and rounds to get a week number.
4. If negative (meaning that the Orig Date day and month is later in the year than today), it creates a new date for LAST year with the same day and month as the Orig. Contract Date, THEN subtracts and divides by 7.

What's the process for pushing this along to resolution?  In a perfect world, I'd like to update this as a patch before the May release - Blackline in particular has been pushing for this, although I gave them the formula to drive off a field they create while they wait.  

I definitely want Prod Eng to validate the formula independently - let me know if you have any questions.

FORMULA:

IF ( 
TODAY() - DATE (YEAR( TODAY () ),MONTH ( JBCXM__OriginalContractDate__c ) , DAY ( JBCXM__OriginalContractDate__c ))>=0, 
FLOOR((TODAY() - DATE (YEAR( TODAY () ),MONTH ( JBCXM__OriginalContractDate__c ) , DAY ( JBCXM__OriginalContractDate__c )))/7)+1, 
FLOOR((TODAY() - DATE (YEAR( TODAY ()-365),MONTH ( JBCXM__OriginalContractDate__c ) , DAY ( JBCXM__OriginalContractDate__c )))/7)+1)

PS.  This started as a chatter feed in GonG.  Snip att'd.



Scott Drost, Gainsight -- Principal Customer Success Architect

    7 replies

    farhat_mirza
    Forum|alt.badge.img
    • farhat_mirza
    • Gainsight Employee ⭐️
    • 15 replies
    • April 29, 2015
    We will evaluate this formula at our end. One problem i see with replacement of existing formula field is that same field is used by Black line (https://jbarasoftware.jira.com/browse/JB-9668) and they are using the formula which we fixed earlier. I would suggest to have another field with different label so that it works with for both the use case. One more suggestion let's continue the conversation on chatter instead of spreading into multiple places.
    elaine_cleary
    • elaine_cleary
    • Helper ⭐️⭐️
    • 303 replies
    • May 1, 2015
    Scott - will this work for contracts closed on "leap day"?  I couldn't resist.  LOL
    sdrostgainsightcom
    Forum|alt.badge.img+3
    • sdrostgainsightcom
    • Author
    • Gainsight Employee ⭐️⭐️⭐️
    • 545 replies
    • May 1, 2015
    Embarrassingly enough, no - just realized that today and haven't written it into the formula yet. Sigh. Hoping to post a correction later this evening. BUT THANKS FOR POINTING OUT MY FLAWS ELAINE!  :)
    Scott Drost, Gainsight -- Principal Customer Success Architect
    elaine_cleary
    • elaine_cleary
    • Helper ⭐️⭐️
    • 303 replies
    • May 1, 2015
    You have no flaws, Scott!!  Even a blind squirrel gets a nut every once and a while...  (I am the blind squirrel in this case and leap day is the nut).  
    farhat_mirza
    Forum|alt.badge.img
    • farhat_mirza
    • Gainsight Employee ⭐️
    • 15 replies
    • May 1, 2015
    Need help how to crack other nut i.e Come up with different name & label for this formula as we cannot change the existing field so from the engineering perspective, we have to add new fields and can only be included in major release.
    farhat_mirza
    Forum|alt.badge.img
    • farhat_mirza
    • Gainsight Employee ⭐️
    • 15 replies
    • May 4, 2015
    Scott any inputs on leap year support and any input on label,field as we need to support both the use case.
    svaughangainsightcom
    We need to get this figured out ASAP.  Every class of Express Workshop includes this content and we don't have a mechanism to correctly enable this behavior so we are having customers configure the rules using the current field knowing that it won't work for Accounts older than 1 year.

    Reply


    Terms & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings