3 Replies Latest reply on Nov 11, 2018 8:59 PM by lalitha p

    Get the count based out of start and End date

    lalitha p

      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.

       

      Report 2)

             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..

        • 1. Re: Get the count based out of start and End date
          Shinichiro Murakami

          Starting from Pivot data

           

           

           

           

           

           

           

           

           

           

          Thanks,

          Shin

          • 2. Re: Get the count based out of start and End date
            sravani adapa

            Hi Lalitha,

             

            I hope this is help full to you.

            Capture.PNG

             

             

            Please find the attached workbook for your reference.

             

             

            Thanks,

            Sravani

            • 3. Re: Get the count based out of start and End date
              lalitha p

              Hi Shin,

               

              I have got the view how i was expected but then i am wondering it is not working with the filters.

              I have 5 filters for this view say location , grade , Sector , Service Line and project etc.,

              Say if I filter for project x i couldn't see all the months rather i can see only months where people have started their project.

              Eg., if i filter out for x then i am getting liek this

              Result :

              Employee   Jan     May

                 101            1         

                 102                       1

              But if you look for 101 employee he stared his project in and continued till Oct which it means All months has to come bcoz i have 102 employee there for whole year starting from may.

              Ideally i am expecting

              Emp     Jan   Feb  Mar   Apr    May   Jun    July   Aug    Sep   Oct   Nov   Dec

              101          1      1     1       1         1        1         1     1         1       1

              102                                              1       1        1       1        1        1     1        1

               

              Regards

              LP