i realized that i have to shape my data for the question below but then before that i want to post here for the solution.
I have data like this.
Employee Project Start Date End Date
101 x 01-01-2018 09-10-2018
102 x 18-05-2018 30-03-2019
103 y 18-05-2018 30-01-2019
104 z 12-02-2017 30-06-2019
105 z 12-02-2017 30-11-2018
The above actually tells me about the employee who is working on a particular project from starting to ending dates.
Here i want to a build a report in such a way that for the whole year i want to show how many employees working in each month of the current year and even at the project level.
Report1: This is the result i need to get from the above data set.
Jan 18 Feb 18 ... May 18 Jun 18 ... Nov 18 Dec 18
3 3 5 5 4 3
If you see i have 3 ppl working in Jan 18(101,104,105 . 104 and 105 have already started in 2017 year which it means their end date is < month showing which is Jan18 so they both should count.
If you see month May 2 more employees 102 and 103 have started their projects so count grows to 5 but if you see month Nov x has left in Oct so i should not count him to month Nov.
here i have one more case: If i look the report at week level i should consider 101 employee only for 1st week of Oct and not the other weeks bcoz he is available only till 9th of Oct.
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
x 1 1 1 1 2 2 2 2 2 2 1 1
y 0 0 0 0 1 1 1 1 1 1 1 1
z 2 2 2 2 2 2 2 2 2 2 2 1
first of all i want to show all months of the current year and then i want to show how many employees are falling/working in that month based on the starting and end dates of the project.
I didnt get any idea how can i get the date first of current year with all the months and then to get this report next.
I tried to create an excel with dates like month and year and then tried to pull out but then dint worked for me..
Any ideas pls..