Part of calculation requires count of days from specific month within a date range

[Zak2]

Hi,

I'm working with sickness data, and need to make calculations for a month. Part of that requires counting the number of days an employee has been absent within the reporting month. This will be multiplied by another number.

In the below simplified example, for each employee I want to count the number of days absent in October 2023. Some start dates precede October 2023, some end dates are after October 2023. Most records in the full data will not have any absence dates in October 2023 at all. 31/12/9999 is an open absence, so for Oct 2023 reporting this end date would be 31/10/2023.

I hope I've explained clearly. Is this doable do you think? If so, I'm guessing it would be within charts/tables rather than the script.

Many thanks

[therealdees]

Maybe something like this?

In script level:

CustomerData:

LOAD

*

,If(Year(AbsenceEndDate) = 9999,

    MakeDate(Year(Today()), Month(AbsenceEndDate), Day(AbsenceEndDate)) - AbsenceStartDate,

          AbsenceEndDate - AbsenceStartDate)   as AbsenceDayCount

From x;

In chart level:

Add a new measure with the same expression:

If(Year(AbsenceEndDate) = 9999,

    MakeDate(Year(Today()), Month(AbsenceEndDate), Day(AbsenceEndDate)) - AbsenceStartDate,

          AbsenceEndDate - AbsenceStartDate)

[Ahidhar]

something like this

tab:
load * Inline
[
Name,Absence Start Date,Absence End Date
John,09/01/2023,09/10/2023
Jane,18/10/2023,31/12/9999
Tom,02/09/2023,06/11/2023
**bleep**,12/10/2023,27/10/2023
];

tab1:
load *,
if(year("Absence End Date")=9999,
Date(today(),'DD/MM/YYYY')-Date("Absence Start Date",'DD/MM/YYYY'),
Date("Absence End Date",'DD/MM/YYYY')-Date("Absence Start Date",'DD/MM/YYYY')) as noofdays
resident tab;
drop table tab;