Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
nsnybs21qv
New Contributor II

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
Tags (1)
1 Solution

Accepted Solutions

Re: How to calculate time difference between rows

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

1 Reply

Re: How to calculate time difference between rows

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

Community Browser