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

How to find the days by comparing with previous date

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

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

There is no 30th in Feb

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

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)

 Name Event Subject Start Date End Date Duration_Availability Praveen maytas 1/1/2015 0:00 11/19/2016 0:00 praveen l&t 10/12/2016 0:00 10/12/2016 0:00 -38 praveen railway 10/22/2016 0:00 11/6/2016 0:00 10 Reddy maytas 8/30/2016 0:00 9/2/2016 0:00 -68 Reddy l&t 9/5/2016 6:00 9/9/2016 13:00 3 Reddy railway 9/5/2016 14:00 9/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

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