Hi all,
Please, I need help with this. Any suggestions/ links/ tutorials will go a long way
I have an employee table with data from Jan 2022 up until Dec 2022 with these columns
Dates worked ( these are date times from Jan 1 to Dec 2022).
Actual number of days worked by each employee in each month from Jan 2022 to Dec 2022 (let's denote it by a)
Average productivity for each employee ( let's denote it by b)
Standard deviation of productivity for each employee (let's denote it by c)
In addition, I have a date table with a column that has the maximum number of working days available in each month. This date table runs from Jan 2022 until Dec 2024 (For example these numbers will be 19, 23, 23 for Jan 2022, Feb 2022 and March 2022; it continues like that until Dec 2024)
What I want to achieve:
Starting Jan 2023, I want to look through the actual number of days worked by each employee from Jan 2022 to Dec 2022 and select the lowest 2nd minimum. ( lets, call this d)
Then, with the lowest 2nd minimum found, I want a random number between the lowest second minimum and the maximum number of working days in Jan 2023. The selected random number will be the actual number of working days for that employee in Jan 2023.
It then looks through from Jan 2022 to Jan 2023, picks the 2nd lowest minimum and generates a random number between this 2nd lowest minimum and the maximum number of working days in Feb 2023. The selected random number will be the actual number of working days for that employee for Feb 2023.
It repeats the same process up until Dec 2023.
At the end of the day, I want to have a table with 3 columns
Year-Month ( For dates worked by employee)
2nd lowest minimum ( For each month from Jan 2023 until Dec 2023)
Productivity = Norm. INV( 40,60)/100, b,c) *d
The goal is to visualize Year-Month and Productivity on a line chart.
Thank you!
