13 Replies Latest reply: Jan 22, 2015 10:57 AM by anbu cheliyan RSS

    Calculating Delay between 2 dates

    Vamsi P

      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

        • Re: Calculating Delay between 2 dates
          saurabh singh

          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]

          • Re: Calculating Delay between 2 dates
            anbu cheliyan

            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;

              • Re: Calculating Delay between 2 dates
                Vamsi P

                No Anbu its not working..

                  • Re: Calculating Delay between 2 dates
                    anbu cheliyan

                    Can you post sample data

                      • Re: Calculating Delay between 2 dates
                        Vamsi P

                        Send me your email address

                        • Re: Calculating Delay between 2 dates
                          anbu cheliyan

                          Can you post sample data in excel file.

                            • Re: Calculating Delay between 2 dates
                              Vamsi P

                              Attached to original post

                                • Re: Calculating Delay between 2 dates
                                  anbu cheliyan

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

                                    • Re: Calculating Delay between 2 dates
                                      Vamsi P

                                      Thankyou anbu ,

                                      Actually i edited the excel and posted here. in the application the fields are in different qvs source files,

                                      how to load the specific fields into a single table we required from that different QVS files,

                                      which method Resident load or apply map will work?

                                        • Re: Calculating Delay between 2 dates
                                          anbu cheliyan

                                          If you have key, value pair then use Apply map else join different QVS sources in script

                                            • Re: Calculating Delay between 2 dates
                                              Vamsi P

                                              Hi Anbu,

                                              I have a error while loading the data. Can u suggest me for the above code. which i executed today as the result is below.

                                               

                                              /////////////////////////// Transformation 1

                                              [SSG Slippage]:

                                              Load

                                              ProjectID_SYS,

                                              [Event Type],

                                              [Event Phase Review Name] as SSG_Event_Phase_Review_Name,

                                              [Change-to Decision] as SSG_Change_to_Decision,

                                              [Event Date],

                                              If([Event Type] = 'Phase Review Date Change', [Event Type], Null()) as SSG_Event_Type ,

                                              date([Change-to Date],'MM/DD/YYYY') as SSG_Forecast_Date,

                                              if(wildmatch([Event Phase Review Name],'*Phase*'),date([Event Date],'MM/DD/YYYY')) as SSG_Event_Date

                                              Resident [Project History];

                                              Left join

                                              LOAD

                                              ProjectID_SYS,

                                              [Project ID] as SSG_PRJ_ID,

                                              [Project Creation Date] as SSG_PRJ_Creation_Date

                                              Resident [Project Details];

                                               

                                              /////////////////////////// Transformation 2

                                              SSG_Slippage_1:

                                              Load *,AutoNumber(RecNo(),SSG_PRJ_ID&SSG_Event_Phase_Review_Name) As RecNo

                                              Resident [SSG Slippage];

                                              Join (SSG_Slippage_1)

                                              Load SSG_PRJ_ID,SSG_Event_Phase_Review_Name,Max(RecNo) As RecNo,If( FirstSortedValue(SSG_Forecast_Date,RecNo) > FirstSortedValue(SSG_PRJ_Creation_Date,RecNo)

                                              And FirstSortedValue(SSG_Event_Date,-RecNo) > FirstSortedValue(SSG_PRJ_Creation_Date,RecNo)

                                              And FirstSortedValue(SSG_Change_to_Decision,-RecNo) ='Pass',Interval(FirstSortedValue(SSG_Event_Date,-RecNo)-FirstSortedValue(SSG_Forecast_Date,RecNo),'d'))

                                              Resident [SSG Slippage]

                                              Group By SSG_PRJ_ID,SSG_Event_Phase_Review_Name;

                                               

                                               

                                              Error showing as RecNo Not found

                                               

                                              Thank you

                                                • Re: Calculating Delay between 2 dates
                                                  anbu cheliyan

                                                  RecNo is not present in table [SSG Slippage]. So you got that error.

                                                   

                                                  Add this to table [SSG Slippage]

                                                  AutoNumber(RecNo(),SSG_PRJ_ID&SSG_Event_Phase_Review_Name) As RecNo


                                                  Why do you have two tables [SSG Slippage] and [SSG Slippage_1] with so many common fields? This will generate synthetic keys. You could have joined result of Firstsortedvalue() to table [SSG Slippage] directly like my script