Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂
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
];
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.
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 🙂
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
];
Thank you so much, thats perfect 🙂
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😐
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 🎅
Hi,
That would have been my suggestion too. I do like a problem that goes away on it's own 😀.
Merry Christmas!
Cheers,
Chris.