check the date in the period

[shilina_klara]

Help, please, beginner!)Sorry for my English

I have a list of employees with their absence in the form:

	Beginning	Expiration
Employee 1	25.04.2017	25.04.2017
Employee 2	17.07.2017	30.07.2017
Employee 3	01.01.2015	31.12.2017
Employee 4	07.10.2016	08.01.2017
Employee 1	11.05.2017	17.05.2017

and there is a reference book with the beginning dates of the week and the names assigned to them, which are used as a sample, for example:

04/17/2017 W16

04/24/2017 W17

05/01/2017 W18

05/05/2017 W19

05/15/2017 W20

and by the choice of the name of the week (W17 for example), I need to count people in the graphs by the type of absence and by the months that this week is included in their period (Beginning-Expiration).

I would be very grateful!

[shilina_klara]

Hello!

I can not give you the app, so I throw off the Excel file, where I tried to visually show what I need.

Thank you !

[MarcoARaymundo]

The kaushik.solanki gave you the tip..Try

CALENDAR:

LOAD * INLINE [

    WeekKey,StartDate,WeekID

    16,17/04/2017,W16

    17,24/04/2017,W17

    18,01/05/2017,W18

    19,05/05/2017,W19

    20,15/05/2017,W20

    21,22/05/2017,W21

    22,29/05/2017,W22

    23,05/06/2017,W23

    24,12/06/2017,W24

];

LOGIN:

LOAD

    //Week(Date(Date#(Begin, 'DD/MM/YYYY')))                        as WeekKey,

    Employee,

    Date(Date#(Begin, 'DD/MM/YYYY')) as Begin,

    Date(Date#(Expiration, 'DD/MM/YYYY')) as Expiration;

LOAD * INLINE [

    Employee,Begin,Expiration

    Employee1,17/04/2017,01/05/2017

    Employee2,17/04/2017,18/04/2017

    Employee3,01/05/2017,31/05/2017

    Employee4,05/05/2017,08/05/2017

    Employee1,15/05/2017,17/05/2017

];

IntervalMatch_Table:

IntervalMatch(StartDate)

LOAD

Begin,

Expiration

RESIDENT LOGIN;