Dear community,
I am trying really hard, but I keep failing with the following problem:
I have a table with the work hours of employees per day of week. Normally, for each employee there is one set of seven entries from Monday to Sunday. But some employees change their hours during the year, so there are multiple sets of entries for those people. My table looks something like this (in this examply, Paul changes from 40 to 32 hours per week beginning Feb 1):
| EmployeeNo | Name | ValidFrom | ValidUntil | Weekday | Hours |
| 1 | Paul | 2021/01/01 | 2021/01/31 | Mon | 8 |
| 1 | Paul | 2021/01/01 | 2021/01/31 | Tue | 8 |
| 1 | Paul | 2021/01/01 | 2021/01/31 | Wed | 8 |
| 1 | Paul | 2021/01/01 | 2021/01/31 | Thu | 8 |
| 1 | Paul | 2021/01/01 | 2021/01/31 | Fri | 8 |
| 1 | Paul | 2021/01/01 | 2021/01/31 | Sat | 0 |
| 1 | Paul | 2021/01/01 | 2021/01/31 | Sun | 0 |
| 1 | Paul | 2021/02/01 | 2021/12/31 | Mon | 8 |
| 1 | Paul | 2021/02/01 | 2021/12/31 | Tue | 8 |
| 1 | Paul | 2021/02/01 | 2021/12/31 | Wed | 8 |
| 1 | Paul | 2021/02/01 | 2021/12/31 | Thu | 8 |
| 1 | Paul | 2021/02/01 | 2021/12/31 | Fri | 0 |
| 1 | Paul | 2021/02/01 | 2021/12/31 | Sat | 0 |
| 1 | Paul | 2021/02/01 | 2021/12/31 | Sun | 0 |
| ... | | | | | |
In my load script I have generated a table that contains the field "Date" with all dates from 2021/01/01 to 2021/12/31 and the field "Weekday". This calendar is linked to the table above by sharing the field "Weekday".
In my visualisation I have a straight table with the dimensions "Date" and "Name" and the intended measure (sum of "hours" for that day):
| Date | Name | Weekday | Hours |
| 2021/01/01 | Paul | Fri | 8 |
| 2021/01/02 | Paul | Sat | 0 |
| 2021/01/03 | Paul | Sun | 0 |
| 2021/01/04 | Paul | Mon | 16 |
| ... | | | |
Obviously, I want Qlik Sense to recognize that for the specific date "2021/01/04" the first values of hours (=8) with the "ValidUntil"="2021/01/31" is relevant, whereas for specific dates after Jan 31, the other value (=0) is relevant.
Can this be done with set analysis in the visualization? Or do I have to make a complete table of all days with all hours for all employees in my load script? I have tried both, but to no avail.
Any help with this issue is highly appreciated! Many thanks in advance!
Tobi
