Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Calculating Delay between 2 dates

Hi All,

Please help me out in writing the expression or at script level.

I need to find the delay in no days.

Based on

1.1st Occurrence date of Forecast date and Last Occurrence date of Event Date and both should be Greater than Creation Date.

2. Decision should be Pass.

3. Also should calculate if Forecast Date of the Stage should be greater than previous pass Event Date

Example - While calculating Phase 2 Col 12 Forecast date is Greater than Col 9 Event date and it is passed at Phase 1 on 10/7/2014

                     

S.noIDNameStageForecast DateEvent DateDecisionCreation DateDelay In No of Days
161PVPhase 19/12/20149/1/2014
261PVPhase 110/5/20149/1/2014
361PVPhase 110/9/20139/1/2014
461PVPhase 111/13/20149/1/2014
561PVPhase 110/3/20149/1/2014
661PVPhase 19/3/2014Pass9/1/2014
761PVPhase 19/3/2014Cancelled9/1/2014
861PVPhase 110/7/2014Pass9/1/2014
961PVPhase 110/7/2014Pass9/1/201425
1061PVPhase 210/10/20149/1/2014
1161PVPhase 210/5/20149/1/2014
1261PVPhase 210/10/20149/1/2014
1361PVPhase 210/8/20149/1/2014
1461PVPhase 29/10/20149/1/2014
1561PVPhase 210/9/20149/1/2014
1661PVPhase 210/7/2014Pass9/1/2014
1761PVPhase 211/11/2014Pass9/1/2014
1861PVPhase 211/17/2014Pass9/1/201438

Thanks in advance.

Message was edited by: Vamsi P

13 Replies
saurabh5
Contributor II

Re: Calculating Delay between 2 dates

hi Vamsi,

in order to calculate the delays you can use floor function like:

=floor(date 1)- floor(date 2) as [delay in no of days]

Not applicable

Re: Calculating Delay between 2 dates

Thankyou saurabh..

I tried it but not working...

anbu1984
Honored Contributor III

Re: Calculating Delay between 2 dates

Temp:

Load Stage,Date#(ForecaseDt,'MM/DD/YYYY') As ForecaseDt,Date#(EvntDt,'MM/DD/YYYY') As EvntDt,Decision,Date#(CreationDt,'MM/DD/YYYY') As CreationDt,AutoNumber(RecNo(),Stage) As RecNo Inline [

Stage,ForecaseDt,EvntDt,Decision,CreationDt

Phase 1,9/12/2014,,,9/1/2014

Phase 1,10/3/2014,,,9/1/2014

Phase 1,,9/3/2014,Pass,9/1/2014

Phase 1,,10/7/2014,Pass,9/1/2014

Phase 2,10/10/2014,,,9/1/2014

Phase 2,9/10/2014,,,9/1/2014

Phase 2,,10/7/2014,Pass,9/1/2014

Phase 2,,11/17/2014,Pass,9/1/2014 ];

Load Stage,If( FirstSortedValue(ForecaseDt,RecNo) > FirstSortedValue(CreationDt,RecNo) And FirstSortedValue(EvntDt,-RecNo) > FirstSortedValue(CreationDt,RecNo)

And FirstSortedValue(Decision,-RecNo) ='Pass',Interval(FirstSortedValue(EvntDt,-RecNo)-FirstSortedValue(ForecaseDt,RecNo),'d')) Resident Temp Group By Stage;

Not applicable

Re: Calculating Delay between 2 dates

No Anbu its not working..

anbu1984
Honored Contributor III

Re: Calculating Delay between 2 dates

Can you post sample data

Not applicable

Re: Calculating Delay between 2 dates

Send me your email address

anbu1984
Honored Contributor III

Re: Calculating Delay between 2 dates

Can you post sample data in excel file.

Not applicable

Re: Calculating Delay between 2 dates

Attached to original post

anbu1984
Honored Contributor III

Re: Calculating Delay between 2 dates

Temp:

Load *,AutoNumber(RecNo(),[Project ID]&[Event Phase Review Name]) As RecNo;

LOAD sno,

    BS,

    BU,

    [Project ID],

    [Project Name],

    [Event Phase Review Name],

    [Forecast date],

    [Event Date],

    [Project Closed],

    [Change-to Decision],

    [Project Creation Date],

    [System Current Phase ID],

    [Phase Slippage],

    Refference

FROM

[smp2.xlsx]

(ooxml, embedded labels, table is Sheet1);

Join(Temp)

Load [Project ID],[Event Phase Review Name],Max(RecNo) As RecNo,If( FirstSortedValue([Forecast date],RecNo) > FirstSortedValue([Project Creation Date],RecNo) And FirstSortedValue([Event Date],-RecNo) > FirstSortedValue([Project Creation Date],RecNo)

And FirstSortedValue([Change-to Decision],-RecNo) ='Pass',Interval(FirstSortedValue([Event Date],-RecNo)-FirstSortedValue([Forecast date],RecNo),'d')) Resident Temp Group By [Project ID],[Event Phase Review Name];