Hi ​@Sowjanya Adiraju 

That’s a challenging question, but in Data Designer or Rules Engine you should be able to accomplish this. One workflow I can think of would be the following:

• Get the full list of records
• Calculate max of created date (grouped if needed, grouping in some way might be needed to merge in the next step; you can use a field that is the same for all records if you just want the overall max)
• Merge the 2 datasets, adding the overall max created date to every record
• Filter the merged list, removing all records where created date matches max created date (if not possible to filter on field=field, then use a date diff transform first)
• Calculate the max of created date on the filtered list

I hope this helps...

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!!