Skip to main content
seth
Expert ⭐️
Jun 15, 2017 2:45 PM UTC

Add 90 Days to Contract Start Date

Has anyone had success using Gainsight to add or subtract days from a date field?

I'm trying to figure out the number of users for a customer in the first 90 days of their contract. (I need to run a comparison against the number of users in the trailing 90 days from today.)

I can grab the contract start date from Opportunity, and I can join it to the Google Analytics MDA data in a Bionic Rule. Then I could limit the Google Analytics data to "Google Analytics Date >= Contract Start AND Google Analytics Date < 90 Days After Contract Start"/

However, I can't figure out a way to add 90 days to the contract start date.
  • Bionic Rule > Transformation Task > +Formula Field: Formula fields don't act on Date fields, so I can't just simply add 90 Days to the Date
  • Rule "Filters": I can say "This field is less/greater than another field", and I can "Add/Subtract N Days from Rule Date", but I can't "Add/Subtract N Days from Date Field"
  • MDA Google Analytics Data: I could add a formula field for "This Pageview's Date plus 90 days", except that MDA object isn't editable
The only thing I can think of is adding a Salesforce Formula Field to the Opportunity, to add 90 Days to the Contract Start Date. However our Salesforce instance is busy with extra fields, so I'd rather not add yet another one unless absolutely necessary.

If you've had any luck manipulating Dates in some other way, I'd love to hear! Thanks for lending your brainpower 🙂

    14 replies

    seth
    sethAuthor
    Expert ⭐️
    Jun 15, 2017 7:33 PM UTC
    Oh man, I so want to get this to work -- it's too pretty to give up on!

    Translate
    AI Transformation @ Gainsight
    karl_rumelhart
    Expert ⭐️
    Jun 16, 2017 4:42 AM UTC
    We obviously need to get formulas on date fields working.  This rule really is insanely cool!  Here is a wicked hack to try: build a table with two columns: first with a list of date vales and a second with those dates plus 90 days. (Could in Excel and then import as CSV or something.)  Then join on the date filed with this table and get a new column with what you want.  
    Translate
    seth
    sethAuthor
    Expert ⭐️
    Jun 16, 2017 1:26 PM UTC
    OH HO! Karl, master hacker extraordinaire! Can't wait to get that set up -- gonna need to print this Rule out and get it framed.
    Translate
    AI Transformation @ Gainsight
    karl_rumelhart
    Expert ⭐️
    Jun 16, 2017 1:35 PM UTC
    Let me know if it works!  
    Translate
    mindy
    Helper ⭐️
    Jan 23, 2019 7:50 PM UTC
    Hey @Seth - have you figured out a better way to do this since you posted? I'm running into the exact same thing and would love to hear that you have a solution

    Translate
    seth
    sethAuthor
    Expert ⭐️
    Jan 23, 2019 8:04 PM UTC
    Hi Mindy! Yes indeed. The feature that's come out since this post, which would have helped me, is the Date Diff function: https://support.gainsight.com/View_More_Categories/GainsightNXT/03Rules_Engine/02Admin_Guides/05Formula_Fields_in_Rules_Engine#Usage_of_Date_Formulas

    Does that work for the use case you have in mind?

    Translate
    AI Transformation @ Gainsight
    mindy
    Helper ⭐️
    Jan 23, 2019 9:59 PM UTC
    Hmm, unfortunately not going to work for my use case. I'm trying to send an auto-renewal notice email and wanted to include the new subscription time frame ("renewal date" - "renewal date + 1 year"). I felt confident that I could add 365 to the renewal date in the bionic rules but when I dug in couldn't actually find a way to do it. Oh well - not the end of the world, but would have been nice. Thanks for the quick response!

    Translate
    seth
    sethAuthor
    Expert ⭐️
    Jan 24, 2019 6:11 PM UTC
    Oof, sorry to hear it! Karl's workaround seems like the best option, then. In the face of a subscription timeframe, though, I've seen that stamped into a custom date field in Salesforce anyways, so that the next renewal date is always extremely accessible, and can be edited for special cases like extensions.

    Translate
    AI Transformation @ Gainsight
    joseph_siudzinski
    Helper ⭐️
    Feb 13, 2019 5:39 PM UTC
    We still really need the ability to add/subtract a number from a date field to return a date. The new bionic formulas are great, but this is a simple and common use case that is missing.

    Translate
    jitin_mehndiratta
    Helper ⭐️⭐️⭐️
    Jun 21, 2019 9:05 AM UTC
    Hi All,

    We now can subtract/Add no. of days/weeks to a particular date.

    Add/ Subtract Date formula is now available in Transformation task of Rules engine.

    Regards,

    Jitin

    Translate