topic Re: Calculation based on dates from a range falling within a month in App Development

Calculation based on dates from a range falling within a month

Anonymous — Wed, 11 Sep 2024 11:56:02 GMT

Hi,

My sickness absence data has rows of absence episodes of varying length, and which can spread across more than one month. I would like to be able to make a calculation based on absence days that fall within a selected month (month/year).

In the below example, the 3 first columns represent an extract from the absence data. How could I calculate the FTE * the number of days that fall in June 2024, as per the final 2 columns? For each row, I need to calculate how many days in each row are in June 2024.

Many thanks

Re: Calculation based on dates from a range falling within a month

Kushal_Chawda — Wed, 11 Sep 2024 14:32:19 GMT

@Anonymous try below

Data: Load *, Floor([Absence Start Date]) &'@'& Floor([Absence End Date]) as Key Inline [ Absence Start Date, Absence End Date, FTE 07/05/2024,02/08/2024,0.5 03/06/2024,24/06/2024,1 28/05/2024,08/06/2024,0.75 15/04/2024,20/05/2024,0.5 11/07/2024, 25/08/2024,1 ]; Calendar: Load *, monthname(Date) as Month; Load *, Date(SubField(Key,'@',1)+IterNo()-1) as Date while SubField(Key,'@',1)+IterNo()-1 <= SubField(Key,'@',2); Load FieldValue('Key',RecNo()) as Key AutoGenerate FieldValueCount('Key');

Re: Calculation based on dates from a range falling within a month

QFabian — Wed, 11 Sep 2024 14:59:50 GMT

@Anonymous , here another simple option to undestand the logic

Data_Aux:

Load * INLINE [

Start, End, FTE

07/05/2024, 02/08/2024, 0.5

03/06/2024, 24/06/2024, 1

28/05/2024, 08/06/2024, 0.75

];

Data:

Load

Required * FTE as Result;

Load

if(month(End) = 6 and month(Start) = 6,

End - Start,

if(month(End) = 6 and month(Start) <> 6,

End - '31/05/2024',

if(month(End) <> 6 and month(Start) = 6,

'01/06/2024' - Start,

if(month(End) <> 6 and month(Start) <> 6,

'31/06/2024' - '01/06/2024'

)))) as Required

Resident Data_Aux;

drop table Data_Aux;

Re: Calculation based on dates from a range falling within a month

madelonjansen — Wed, 11 Sep 2024 15:09:08 GMT

Do you need only june, or do you need this every month?

For a more flexible solution you could do this:

AbsencePerMonth; Load Sum(FTE) as Result, StartDate, EndDate, AbsenceMonth, EmployeeID Group By StartDate, EndDate, AbsenceMonth, EmployeeID; Load StartDate, EndDate, MonthName(StartDate + iterno()-1) as AbsenceMonth, FTE, EmployeeID Resident absence While Date(StartDate + iterno()-1) <= EndDate; Drop Table absence;