Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calculation based on dates from a range falling within a month

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.

AbsenceCalc.PNG

Many thanks

Labels (1)
3 Replies
Kushal_Chawda

@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');

 

Screenshot 2024-09-11 at 15.31.39.png

 

 

QFabian
MVP
MVP

@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;
QFabian_0-1726066765764.png

 

Greetings!! Fabián Quezada (QFabian)
did it work for you? give like and mark the solution as accepted.
madelonjansen
Partner Ambassador
Partner Ambassador

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;