Calculating Teleworking Rate Based On Dates

[Fight4Freedom]

Hi everyone. I have the leave start date, end date and leave day information which have been calculated on a sql table. Leave days are all networkdays. I need to calculate teleworking rate for each month of year. (teleworking days / networkdays)

Some of them contain the same month like blue painted ones. So, It's easy to calculate how many leave days each month contains. But, orange painted ones' starts date and end date contain different month. I need breaking down leave days into months and unify and summarize on a monthly basis like second picture.

Thanks.

[ramchalla]

Hi,

Can you please check the below if it helps. Please find the attached qvw app also.

TEST:

LOAD * Inline[

START_DATE, END_DATE

10/02/2025, 18/02/2025

06/01/2025, 13/01/2025

27/11/2024, 06/12/2024

30/10/2024, 05/11/2024 

];

TEST1:

LOAD 

START_DATE, END_DATE ,

Date(MonthStart(END_DATE), 'MMM YYYY') as PERIOD,

NetWorkDays( START_DATE, END_DATE ) as [LEAVE DAY]

Resident TEST

//Where MonthStart(START_DATE) = MonthStart(END_DATE)

;

Concatenate

LOAD 

//START_DATE, END_DATE ,

Date(MonthStart(END_DATE), 'MMM YYYY') as PERIOD,

NetWorkDays( START_DATE, END_DATE ) as [LEAVE DAY],

Date(Floor( MonthStart(END_DATE)),'DD/MM/YYYY') as START_DATE,

Date(Floor( MonthEnd(START_DATE)),'DD/MM/YYYY') as END_DATE

Resident TEST

Where MonthStart(START_DATE) <> MonthStart(END_DATE);

DROP Table TEST;