How to calculate time difference between rows

[Anonymous]

Hi

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.

Thanks!

Zixiao 

Opportunity ID	From Stage	To Stage	Amount	Last Modified	Days in Stage
006300000030OoO		1 - Qualification		8/31/2005	0
006300000030OoO	1 - Qualification	1 - Qualification	500000	10/12/2005	42
006300000030OoO	1 - Qualification	3 - Proposal/Engagement Letter	500000	10/25/2005	13
006300000030OoO	3 - Proposal/Engagement Letter	3 - Proposal/Engagement Letter	500000	9/5/2006	315
006300000030Oto		3 - Proposal/Engagement Letter		8/31/2005	0
006300000030Oto	3 - Proposal/Engagement Letter	3 - Proposal/Engagement Letter	125000	8/31/2005	0
006300000030Oto	3 - Proposal/Engagement Letter	4 - Contracting	125000	11/22/2005	83
00630000004rUjN		1 - Qualification		2/13/2006	0
00630000004rUjN	1 - Qualification	2 - Value Proposition	16000	5/18/2006	94
00640000007xjFa		1 - Qualification		8/30/2006	0
00640000007xjFa	1 - Qualification	2 - Value Proposition	100000	9/30/2006	31

[sunny_talwar · Accepted solution]

Try the following script:

Table:

LOAD [Opportunity ID],

    [From Stage],

    [To Stage],

    Amount,

    Date#([Last Modified]) as [Last Modified],

    [Days in Stage]

FROM

[https://community.qlik.com/thread/171408]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

  If([Opportunity ID] = Peek('Opportunity ID'), [Last Modified] - Peek('Last Modified'), 0) as [Days in Stage1]

Resident Table

Order By [Opportunity ID], [Last Modified];

DROP Table Table;

Output: