1 2 Previous Next 25 Replies Latest reply on Nov 9, 2016 1:34 AM by praveen reddy

    How to find iam over booked

    praveen reddy

      Hi Team,

       

      We are facing 2 issues:

      1. we have created a logic for calculating the availability of a person from one project to another project this we have done based on below logic :

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

         here the issue is we are not fix the person while calculating the availability ( for the very first project it is taking as null duration availability where as coming to second person as the duration availability should be null for the 1st project but it is considering previous person last project end date, due to this we are not able to show correct results)

         

      TechnicianEVENT_IDStart DateEnd DateDuration_Availability
      _DUTRONQUAY, Cédrica39A0000003lO6JIAU9/2/2016 0:009/15/2016 0:00null
      _DUTRONQUAY, Cédrica39A0000000UokBIAS9/20/2016 0:009/30/2016 0:00    5
      _DUTRONQUAY, Cédrica39A00000003Vh1IAE10/10/2016 0:0011/4/2016 0:00    10
      _DUTRONQUAY, Cédrica39A00000003WuCIAU11/5/2016 0:0011/6/2016 0:00    1
      _LAMACZ, Juliena39A0000003lPTZIA29/14/2016 0:0010/13/2016 0:00   -53
      _LAMACZ, Juliena39A00000003VixIAE10/14/2016 0:0010/27/2016 0:00      1
      _LAMACZ, Juliena39A00000003WwXIAU10/28/2016 0:0011/11/2016 0:001

       

       

      2. How to filter out the persons with improper assignment of projects ( if he dont have series of projects assigned or any projects assigned without completion of previous project)

       

         

      TechnicianEVENT_IDStart DateEnd DateDuration_Availability
      Adamson, Williama39A0000000UUiYIAW1/1/2015 0:0011/19/2016 0:00
      Adamson, Williama39A00000003WNCIA210/12/2016 0:0010/12/2016 0:00-38
      Adamson, Williama39A00000003UgaIAE10/22/2016 0:0011/6/2016 0:0010
      ALIOUANE Omara39A0000000O5FcIAK8/29/2016 7:009/5/2016 6:59-69
      ALIOUANE Omara39A0000003lO2lIAE8/31/2016 0:009/1/2016 0:00-5
      ALIOUANE Omara39A000000AoALGIA39/5/2016 7:009/12/2016 7:004
      ALIOUANE Omara39A0000003lPKNIA29/12/2016 0:009/14/2016 0:000
      ALIOUANE Omara39A0000003lPKIIA29/18/2016 0:009/21/2016 0:004
      ALIOUANE Omara39A00000003UhJIAU9/26/2016 0:009/27/2016 0:005
      ALIOUANE Omara39A00000003VB4IAM10/3/2016 0:0010/7/2016 0:006
      ALIOUANE Omara39A00000003VKuIAM10/10/2016 0:0010/13/2016 0:003
      ALIOUANE Omara39A00000003VqNIAU10/17/2016 0:0010/28/2016 0:004
      ALIOUANE Omara39A00000003UeZIAU10/28/2016 7:0011/2/2016 8:000
      ALIOUANE Omara39A0000003lGV5IAM10/31/2016 0:0010/31/2016 0:00-2
      ALIOUANE Omara39A0000003lGTTIA211/1/2016 0:0011/1/2016 0:001
      ALIOUANE Omara39A000000Ao9UCIAZ11/11/2016 0:0011/11/2016 0:0010
      ALIOUANE Omara39A0000003lPH4IAM11/14/2016 0:0011/19/2016 0:003

       

       

      Attached workbook

        • 1. Re: How to find iam over booked
          Norbert Maijoor

          Hi Praveen,

           

          Find my approach as reference below and stored in attached workbook version 9.3

           

          • 2. Re: How to find iam over booked
            praveen reddy

            Norbert MaijoorForums

             

            Hi Team,

             

            We are facing 2 issues:

            1. we have created a logic for calculating the availability of a person from one project to another project this we have done based on below logic :

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

               here the issue is we are not fix the person while calculating the availability ( for the very first project it is taking as null duration availability where as coming to second person as the duration availability should be null for the 1st project but it is considering previous person last project end date, due to this we are not able to show correct results)

                

            TechnicianEVENT_IDStart DateEnd DateDuration_Availability
            _DUTRONQUAY, Cédrica39A0000003lO6JIAU9/2/2016 0:009/15/2016 0:00null
            _DUTRONQUAY, Cédrica39A0000000UokBIAS9/20/2016 0:009/30/2016 0:00    5
            _DUTRONQUAY, Cédrica39A00000003Vh1IAE10/10/2016 0:0011/4/2016 0:00    10
            _DUTRONQUAY, Cédrica39A00000003WuCIAU11/5/2016 0:0011/6/2016 0:00    1
            _LAMACZ, Juliena39A0000003lPTZIA29/14/2016 0:0010/13/2016 0:00   -53
            _LAMACZ, Juliena39A00000003VixIAE10/14/2016 0:0010/27/2016 0:00      1
            _LAMACZ, Juliena39A00000003WwXIAU10/28/2016 0:0011/11/2016 0:001

             

             

            2. How to filter out the persons with improper assignment of projects ( if he dont have series of projects assigned or any projects assigned without completion of previous project)

             

                

            TechnicianEVENT_IDStart DateEnd DateDuration_Availability
            Adamson, Williama39A0000000UUiYIAW1/1/2015 0:0011/19/2016 0:00
            Adamson, Williama39A00000003WNCIA210/12/2016 0:0010/12/2016 0:00-38
            Adamson, Williama39A00000003UgaIAE10/22/2016 0:0011/6/2016 0:0010
            ALIOUANE Omara39A0000000O5FcIAK8/29/2016 7:009/5/2016 6:59-69
            ALIOUANE Omara39A0000003lO2lIAE8/31/2016 0:009/1/2016 0:00-5
            ALIOUANE Omara39A000000AoALGIA39/5/2016 7:009/12/2016 7:004
            ALIOUANE Omara39A0000003lPKNIA29/12/2016 0:009/14/2016 0:000
            ALIOUANE Omara39A0000003lPKIIA29/18/2016 0:009/21/2016 0:004
            ALIOUANE Omara39A00000003UhJIAU9/26/2016 0:009/27/2016 0:005
            ALIOUANE Omara39A00000003VB4IAM10/3/2016 0:0010/7/2016 0:006
            ALIOUANE Omara39A00000003VKuIAM10/10/2016 0:0010/13/2016 0:003
            ALIOUANE Omara39A00000003VqNIAU10/17/2016 0:0010/28/2016 0:004
            ALIOUANE Omara39A00000003UeZIAU10/28/2016 7:0011/2/2016 8:000
            ALIOUANE Omara39A0000003lGV5IAM10/31/2016 0:0010/31/2016 0:00-2
            ALIOUANE Omara39A0000003lGTTIA211/1/2016 0:0011/1/2016 0:001
            ALIOUANE Omara39A000000Ao9UCIAZ11/11/2016 0:0011/11/2016 0:0010
            ALIOUANE Omara39A0000003lPH4IAM11/14/2016 0:0011/19/2016 0:003
            • 3. Re: How to find iam over booked
              Norbert Maijoor

              Hi Praveen,

               

              Find my approach as reference for your first mentioned challenge below and stored in attached workbook version 9.3

               

              • 4. Re: How to find iam over booked
                Norbert Maijoor

                HI Praveen,

                 

                Find my approach as reference below and stored in attached workbook version 9.3

                It's a matter now of defining the possible 'scenario" and incorporate it in the calculation field "Availability Checker" and defining it at as filter.

                 

                1 of 1 people found this helpful
                • 5. Re: How to find iam over booked
                  praveen reddy

                  Hi Norbert

                   

                  I like your approach. we did the same in our dashboard. it would be a problem if we keep Availability Tracker. For each person ,he might be assigned to 10 different projects. we would get different numbers of availability .we cant incorporate filter for user to check on the availability.

                   

                  one of problem is , i would like to make him as over booked , if he assigned to project before completion of previous project and that project end date is in future.if he has one project like that i would like assign all the records as overbooked.

                   

                  it would be great help for us , if you provide solution for this.

                  • 6. Re: How to find iam over booked
                    praveen reddy

                    Norbert Maijoor

                     

                    Hi Norbert

                     

                    Please help me on this

                     

                    Regards

                    Praveen Reddy

                    • 7. Re: How to find iam over booked
                      Norbert Maijoor

                      Praveen,

                       

                      Find my approach as reference below

                       

                      Which consultants have availability between projects?

                       

                       

                       

                       

                      Which consultant are available after last projects at a earliest date? (play with endDate)

                       

                       

                      • 8. Re: How to find iam over booked
                        praveen reddy

                        Hi Norbert

                         

                        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:

                         

                        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

                         

                        Screen-1 --we are able to achieve this

                         

                        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 --we are able to EXPECTING BELOW  ---- IF IT HAS ATLEAST 1 RECORD WHICH IS OVERLAPPING , IT SHOULD MAKE COMPLETE RECORDS AS OVER BOOKED

                         

                        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

                        • 9. Re: How to find iam over booked
                          praveen reddy

                          Norbert Maijoor

                           

                          Kindly request you to please help me on this

                          • 10. Re: How to find iam over booked
                            praveen reddy

                            Norbert Maijoor

                             

                            Please help me on this

                            • 11. Re: How to find iam over booked
                              Norbert Maijoor

                              Hi Praveen,

                               

                              On which "Screen" should i focus. Screen 2?

                              • 12. Re: How to find iam over booked
                                praveen reddy

                                Hi Norbert

                                 

                                Please concentrate on screen-2

                                 

                                we make to make all records of that technician as overbooked , if he has atleast one event which is overlapped.

                                 

                                Screen-2 --we are able to EXPECTING BELOW  ---- IF IT HAS ATLEAST 1 RECORD WHICH IS OVERLAPPING , IT SHOULD MAKE COMPLETE RECORDS AS OVER BOOKED

                                 

                                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

                                 

                                 

                                Regards

                                Praveen Reddy

                                • 13. Re: How to find iam over booked
                                  Norbert Maijoor

                                  Praveen,

                                   

                                  I will have a look at it during the weekend.

                                  • 14. Re: How to find iam over booked
                                    praveen reddy

                                    Thanks a lot for your kind Help.Really appreciate your efforts.

                                    1 2 Previous Next