This is requirement to calculate the total availability in hours of employees for a range of dates.
This range of dates would come from a Date table from the date picker.
Now we have to calculate the no of hours worked as marked in grey in the excel sheet.The ones in white are the hours which would be present in the DB.
For eg if we take Tuesday , the Effective period start date is 09/01/20 & the Effective period end date is 09/14/20 that means the hrs worked by that employee is 9 hrs as of 09/01/20 & it would continue to be the same till 09/14/20.Then on 09/14 it again changes to 7hrs without any further updates to the record.
Hence from QlikSense(QS) if a date selection is done from 09/01-09/30 then it would first check all the Tuesdays between 09/01 & 09/14 ( it comes to 09/08 ) & add up all the 9 hrs & then it would check all the Tuesdays >= 09/14 ( it comes to 09/21 & 09/28) & add up all the 7 hrs.
So the net Tuesday hours should be ideally as below:
09/01 (From DB)= 9hrs
09/08 (From QS)=9hrs
09/14 (From DB)=7hrs
09/21 (From QS)=7hrs
09/28 (From QS)=7hrs
Hence the total hrs calculated for a employee for the selected date range 09/01-09/30 for Tuesday = 9+9+7+7+7=39hrs
Can use the following logic for the dates calculation:
"(calendar date between Effective period start date and Effective period end date)
OR (calendar date >= Effective period start date AND Effective period end date is null)"
All these had to done in some sort of QS virtual table as this process would continue across all the weekdays & for all the employees within the range of dates selected by the user.
Now what I would request from the forum is what would be the code to acheive this goal i.e how to store this no. of hours in this virtula table so that later on I can use a SUM to calculate the total hours worked by all the employees.
Please reply urgently.
Attaching the sample sheet.
