Determine the number of Ill people over a period of time

[schuffe]

Hello everyone, I have the following problem:

I have a table with the following 3 columns:

Person | Date start | Date end

The Dates are representing the time period, where a person was ill.

For example:   A | 03.12.2020 | 07.12.2020

Now i want to do the following things: 

- a bar chart, which shows the number of Ill people over a period of time 

- a KPI with the average number of Ill people over a period of time 

I know how to do it for just one day X:

count({ [Date start] = {"<=X"},[Date end] = {">=X"}>} Person)

But I dont know how to handle more than one day. I hope you can help 🙂

[chrismarlow]

Hi,

If you add a calendar table (search the community for master calendar to get a script, I just added a few dates in my toy application attached by hand) you can use the date column in that as a dimension (similar to chart on attached). For your KPI you may need to wrap an AGGR around & would need to know what basis you are taking the average over.

Cheers,

Chris.

[schuffe]

Hello and Thanks for the answer.

But I think I still need some help.

I cant open the qvf-file, or at least I dont know how to do it because I am just working with the demo Qlik-Sense hub version and not with Qlik-Sense Desktop.

In theory I know how to make a Master calendar, but I am not sure how to use it for my example.

I would like to focus on the KPI, because I think that this is the more difficult problem.

I also prepared some Data to make it more clear:

I would like to know, how many employees were ill (average) from 05.01.2020 - 08.01.2020

The KPI would show 4.5 

Maybe someone can describe me how i can handle it.

Thanks in advance 🙂

[chrismarlow · Accepted solution]

Hi,

Try something like Avg(aggr(Sum(If(Date>=[Date start] AND Date<=[Date end],1,0)),[Date])) on a toy application with script below. Gives me;

Cheers,

Chris.

Script - I have a different date format - the 'dates' table you would generate like a master calendar, but note is disconnected from the fact table (data);

data:
load * inline [
Person, Date start, Date end
A, 01/01/2020, 12/01/2020
B, 04/01/2020, 08/01/2020
C, 02/01/2020, 02/01/2020
C, 04/01/2020, 07/01/2020
C, 10/01/2020, 13/01/2020
D, 03/01/2020, 11/01/2020
D, 13/01/2020, 13/01/2020
E, 06/01/2020, 09/01/2020
];

dates:
load * inline [
Date
01/01/2020
02/01/2020
03/01/2020
04/01/2020
05/01/2020
06/01/2020
07/01/2020
08/01/2020
09/01/2020
10/01/2020
11/01/2020
12/01/2020
13/01/2020
];

[schuffe]

Thank you so much, thats perfect 🙂

[schuffe]

I am so Sorry that I have to ask again, but I need one more extension 😬

I also have a column "gender" with "men" and "women". If I want to filter only for men I thought it´s going to be like this:

Avg(aggr(Sum(If(Date>=[Date start] AND Date<=[Date end] AND gender = {"men"},1,0)),[Date]))

But this is not working. Sorry that I cant get it by myself😐

[schuffe]

After a bit of trying i found the solution:

Avg(aggr(Sum( {<gender = {"men"}>} If(Date>=[Date start] AND Date<=[Date end],1,0)),[Date]))

Merry Christmas 🎅

[chrismarlow]

Hi,

That would have been my suggestion too. I do like a problem that goes away on it's own 😀.

Merry Christmas!

Cheers,

Chris.