1 Reply Latest reply: Jul 6, 2015 4:42 PM by Sunny Talwar RSS

    How to calculate time difference between rows

    Yvonne Han



      I'm working with Salesforce pipeline data. I need to know for each opportunity ID, how long it stays in each stage. My data are shown below. Can someone help with the script to calculate the Days in Stage column. Basically, it should be 0 if an opportunity doesn't have a "From Stage" which indicates it is just created. Other rows, it should be the difference between its 'Last Modified" date and the date that's immediately before it with the same opportunity ID.










      Opportunity IDFrom StageTo StageAmountLast ModifiedDays in Stage
      006300000030OoO 1 - Qualification 8/31/20050
      006300000030OoO1 - Qualification1 - Qualification50000010/12/200542
      006300000030OoO1 - Qualification3 - Proposal/Engagement Letter50000010/25/200513
      006300000030OoO3 - Proposal/Engagement Letter3 - Proposal/Engagement Letter5000009/5/2006315
      006300000030Oto 3 - Proposal/Engagement Letter 8/31/20050
      006300000030Oto3 - Proposal/Engagement Letter3 - Proposal/Engagement Letter1250008/31/20050
      006300000030Oto3 - Proposal/Engagement Letter4 - Contracting12500011/22/200583
      00630000004rUjN 1 - Qualification 2/13/20060
      00630000004rUjN1 - Qualification2 - Value Proposition160005/18/200694
      00640000007xjFa 1 - Qualification 8/30/20060
      00640000007xjFa1 - Qualification2 - Value Proposition1000009/30/200631