How to get Second of max created date

Thanks ​@Jef Vanlaer 

​@Sowjanya Adiraju I have an easy and direct way to get you this number.

To summarize your need: want to take the MAX of a grouped date, then subtract 1 day.

Quick idea: from your MAX date dataset, you can run 3 transforms converting EPOCH to get the ‘-1 day’ value.

1. This can be done in a RULE or a DATA DESIGNER
2. Build a dataset to get your MAX date value
3. From this final dataset, add a TRANSFORM task
   1. Add a FORMULA FIELD: Number type
      1. For this Number formula, choose FUNCTION
         1. DATE OR TIME TO EPOCH
            1. Choose the ‘MAX date’ field
            2. Set the default to 0
      2. Save task
4. Add a second TRANSFORM task
   1. Add a FORMULA FIELD
      1. For this calculation, choose NUMERIC EXPRESSION
         1. the formula field from previous TRANSFORM
         2. operator is ‘-’ (minus)
         3. enter number ‘86400’ (number of seconds in a day)
      2. Save task
5. Add a third TRANSFORM task
   1. Add a FORMULA FIELD: Date
      1. For this Date formula, choose FUNCTION
         1. Epoch to Date
            1. Choose your ‘Numeric Expression’ formula filed
            2. You can set any date you want in this format yyyy-mm-dd (i did 1970/1/1)
      2. Save this task
6. In your preview of that final transform task, you should see your original date field and the new calculated date field of -1.

Please let me know if you have any questions!!

Christopher

ECCS Group

As a second option, if you are actually looking for the 2nd from the top of MAX dates, i.e.

• 7/1/24
• 8/1/24
• 9/1/24

The next easiest way to manage this moving forward is to build 2 fields on an object, probably COMPANY or RELATIONSHIP, to capture the CURRENT and PREVIOUS max values of your dates.

Then this is the hardest part. You can try populating the values for those fields based on Jef’s idea, or you can set the current values and make this process work moving forward, or you can set them manually. Which ever way, you need values in CURRENT MAX DATE field.

Then build a rule where you get the current MAX Date value, but you also merge that with your COMPANY or RELATIONSHIP fields. Then do a compare of CURRENT MAX VALUE and the dataset MAX value. If they are the same, you do nothing. IF they are different:

• Update PREVIOUS MAX DATE with CURRENT MAX DATE value
• Push the data set value you calculated in your rule to the CURRENT MAX DATE

The one big issue with this rule is that it doesn’t look historically; it really only works moving forward. Unless you put in the effort to set those historic values.

However, for reporting purposes you now have the PREVIOUS MAX DATE field that you can add to reporting, rules, etc.

Christopher

ECCS Group

Thanks ​@Jef Vanlaer and ​@christopher_sanderson 
​@christopher_sanderson Actually i want to find the second highest date among the list. 
I could accomplish this SS below. My first experience in Gainsight CS Community. Cheers!!