Calculating Delay between 2 dates

[#1]

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.no	ID	Name	Stage	Forecast Date	Event Date	Decision	Creation Date	Delay In No of Days
1	61	PV	Phase 1	9/12/2014			9/1/2014
2	61	PV	Phase 1	10/5/2014			9/1/2014
3	61	PV	Phase 1	10/9/2013			9/1/2014
4	61	PV	Phase 1	11/13/2014			9/1/2014
5	61	PV	Phase 1	10/3/2014			9/1/2014
6	61	PV	Phase 1		9/3/2014	Pass	9/1/2014
7	61	PV	Phase 1		9/3/2014	Cancelled	9/1/2014
8	61	PV	Phase 1		10/7/2014	Pass	9/1/2014
9	61	PV	Phase 1		10/7/2014	Pass	9/1/2014	25
10	61	PV	Phase 2	10/10/2014			9/1/2014
11	61	PV	Phase 2	10/5/2014			9/1/2014
12	61	PV	Phase 2	10/10/2014			9/1/2014
13	61	PV	Phase 2	10/8/2014			9/1/2014
14	61	PV	Phase 2	9/10/2014			9/1/2014
15	61	PV	Phase 2	10/9/2014			9/1/2014
16	61	PV	Phase 2		10/7/2014	Pass	9/1/2014
17	61	PV	Phase 2		11/11/2014	Pass	9/1/2014
18	61	PV	Phase 2		11/17/2014	Pass	9/1/2014	38

Thanks in advance.

Message was edited by: Vamsi P

[saurabh5]

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]

[#3]

Thankyou saurabh..

I tried it but not working...

[anbu1984]

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;

[#5]

No Anbu its not working..

[anbu1984]

Can you post sample data

[#7]

Send me your email address

[anbu1984]

Can you post sample data in excel file.

[#9]

Attached to original post

[anbu1984]

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];