3 Replies Latest reply on Nov 4, 2016 4:20 AM by praveen reddy

    How to find the days by comparing with previous date

    praveen reddy

      Hi All

       

      I have a question here.

       

      I want to find out the days which i am free from one project to other project.

       

      Name        Project       start-Date       end-Date          Expecting

      Naveen     Amazon     1-Jan-2016     18-Jan-2016     12 -Days    (30-Feb-2016-18-Jan-2016  )

                       Flipkart       30-Feb-2016  30-Mar-2016     30-Days     (1-May-2016 -30-Mar-2016  )

                       shopclues   1-May-2016    30-Aug-2016     30-Days    (30-Aug-2016-30-Sep-2016)

                       ibibo            30-Sep-2016   1-Oct-2016        0-Days

       

      Please provide me solution to do this tableau

        • 1. Re: How to find the days by comparing with previous date
          ra.sae.0

          Hi Praveen,

           

          PFB Formula.

           

          min([Start Date]) - LOOKUP(min([End Date]),-1)

           

           

          But as you said as Previous date, then I guess it should be

          LOOKUP(min([Start Date]),1) - min([End Date])

           

          Please try either of them and hope you get your expectations

           

          There is no 30th in Feb

          • 2. Re: How to find the days by comparing with previous date
            praveen reddy

            Hi Rasa

             

            Thanks alot for the solution .

             

            we have used below logic to get the duration availability

             

            min([Start Date])-LOOKUP(MAX([End Date]),-1)

             

            NameEvent SubjectStart DateEnd DateDuration_Availability
            Praveenmaytas1/1/2015 0:0011/19/2016 0:00
            praveenl&t10/12/2016 0:0010/12/2016 0:00-38
            praveenrailway10/22/2016 0:0011/6/2016 0:0010
            Reddymaytas8/30/2016 0:009/2/2016 0:00-68
            Reddyl&t9/5/2016 6:009/9/2016 13:003
            Reddyrailway9/5/2016 14:009/12/2016 6:00-4

             

            we are facing one more issue here . we are not able to fix the technician as we have used LOOKUP function.

             

            we are expecting for every individual name , first record duration availability should be null.could you please suggest us on solutioning this kind of issues

            • 3. Re: How to find the days by comparing with previous date
              praveen reddy

              Hi

               

              We have a scenario to calculate the duration availability of a technician between one project to the other, where in some technicians are assigned to the projects properly in series (after completion of previous assignment) while some are not properly assigned (there is overlap in assignment) as below.

               

              We are calculating duration Based on Previous assignment end date and Next assignment start date:

               

              CALCUALTION USED FOR DURATION -- attr([End Date])<[Start Next Project] then [Start Next Project]-attr([End Date]) END

               

              Technician

              Project

              Start Date

              End Date

              Duration

              _DUTRONQUAY, Cédric

              CHINA SHIPPING

              9/2/2016 0:00

              9/15/2016 0:00

              5

              INDISPONIBLE

              9/20/2016 0:00

              9/30/2016 0:00

              10

              FREEPORT MASSA

              10/10/2016 0:00

              10/28/2016 0:00

              1

              _

              PACIFICA

              10/29/2016 0:00

              10/30/2016 0:00

              null

              ALIOUANE Omar

              Astreinte Wind; FY2016; FW35

              8/29/2016 7:00

              9/5/2016 6:59

              -5

              C-006779-1-2;EDF - Le Carnet Offshore Wind Farm;

              8/31/2016 0:00

              9/1/2016 0:00

              4

              Astreinte Système client sous contrat;FY2016;FW36

              9/5/2016 7:00

              9/12/2016 7:00

              0

              C-011823;GRTgaz - Cherré Compressor station;

              9/12/2016 0:00

              9/14/2016 0:00

              4

              C-011561;Total Infrastructures Gaz France ( TIGF ) - Lussagnet

              9/18/2016 0:00

              9/21/2016 0:00

              5

              C-006779-2-1;BELWIND OOSTENDE;

              9/26/2016 0:00

              9/27/2016 0:00

              6

              C-006779-2-1;BELWIND OOSTENDE;

              10/3/2016 0:00

              10/7/2016 0:00

              3

              C-006779-2-1;BELWIND OOSTENDE;

              10/10/2016 0:00

              10/13/2016 0:00

              4

              C-006779-6-5;BELWIND OOSTENDE;

              10/17/2016 0:00

              10/21/2016 0:00

              3

              Astreinte Syst. client sous contrat;FY2016;FW43

              10/24/2016 7:00

              11/2/2016 8:00

              -2

              Congés

              10/31/2016 0:00

              10/31/2016 0:00

              1

              Jour Férié

              11/1/2016 0:00

              11/1/2016 0:00

              10

              Jour Férié

              11/11/2016 0:00

              11/11/2016 0:00

              3

              C-011302;Air Liquide - Severstal Industrial Gases Plant;

              11/14/2016 0:00

              11/19/2016 0:00

              null

               

               

              We are looking for a solution to have a flag field to identify the technicians who are overbooked and who are normally assigned as below.

               

              Currently we are using below calculation to achieve it, but it is giving overbooked flag only to the projects where there is project overlap or negative duration:

               

              if attr([Technician])=lookup(attr([Technician]),1) and

               

              attr(End Date])>[Start Next Project] then "Overbooked" ELSE "Available" END

               

              By  above condition we are able to achieve like below shown in screen 1----  but we are trying to update all the records of that techinician as over booked shown in screen -2 ‘

               

              SCREEN-1

               

              Technician

              Project

              Start Date

              End Date

              Availabity checker

              Flag

              _DUTRONQUAY, Cédric

              CHINA SHIPPING

              9/2/2016 0:00

              9/15/2016 0:00

              5

              Available

              INDISPONIBLE

              9/20/2016 0:00

              9/30/2016 0:00

              10

              Available

              FREEPORT MASSA

              10/10/2016 0:00

              10/28/2016 0:00

              1

              Available

              PACIFICA

              10/29/2016 0:00

              10/30/2016 0:00

              null

              Available

              ALIOUANE Omar

              Astreinte Wind; FY2016; FW35

              8/29/2016 7:00

              9/5/2016 6:59

              -5

              Over Booked

              C-006779-1-2;EDF - Le Carnet Offshore Wind Farm;

              8/31/2016 0:00

              9/1/2016 0:00

              4

              Available

              Astreinte Système client sous contrat;FY2016;FW36

              9/5/2016 7:00

              9/12/2016 7:00

              0

              Available

              C-011823;GRTgaz - Cherré Compressor station;

              9/12/2016 0:00

              9/14/2016 0:00

              4

              Available

               

              SCREEN-2 – Expecting RESULTS

               

              Technician

              Project

              Start Date

              End Date

              Availabity checker

              Flag

              _DUTRONQUAY, Cédric

              CHINA SHIPPING

              9/2/2016 0:00

              9/15/2016 0:00

              5

              Available

              INDISPONIBLE

              9/20/2016 0:00

              9/30/2016 0:00

              10

              Available

              FREEPORT MASSA

              10/10/2016 0:00

              10/28/2016 0:00

              1

              Available

              PACIFICA

              10/29/2016 0:00

              10/30/2016 0:00

              null

              Available

              ALIOUANE Omar

              Astreinte Wind; FY2016; FW35

              8/29/2016 7:00

              9/5/2016 6:59

              -5

              Over Booked

              C-006779-1-2;EDF - Le Carnet Offshore Wind Farm;

              8/31/2016 0:00

              9/1/2016 0:00

              4

              Over Booked

              Astreinte Système client sous contrat;FY2016;FW36

              9/5/2016 7:00

              9/12/2016 7:00

              0

              Over Booked

              C-011823;GRTgaz - Cherré Compressor station;

              9/12/2016 0:00

              9/14/2016 0:00

              4

              Over Booked

               

              Norbert Maijoor