Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to calculate time difference between rows

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 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
1 Solution

Accepted Solutions
sunny_talwar

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:


Capture.PNG

View solution in original post

1 Reply
sunny_talwar

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:


Capture.PNG