I have a project to show a dashboard work effort for teams in our organization. Attached is the Qlikview app with data.
I need to calculate the number of hours per week a person works.
This depends on 2 factors:
1. The number of Standard Work time entered
2. If there are multiple entries for a user in the STAFF table, then this means, his standard work time has changed. Therefore, I need to calculate his time from Beginning of 2016 until his End_date and then calculate his work time with the 2nd row.
In order to take into account the public holidays, the STAFF table, I figured the only possible way to do this would be to create a new table in the load script that would have the fields: WeekNo - Year - Staff login - standard work hours - # of working days - # of working hours
# of working days would be the # of days in a working week - the # of public holidays
The # of working hours would be # of working days * standard work hours
I looked into different possibilities but I'm not that advanced.
In order to create the new table with the different fields from different sources, should I use the for / next? And how do I insert a row per week per user in this new table?
