7 Replies Latest reply: Jun 30, 2017 6:25 AM by Arvind Patil RSS

    Employees HeadCount

    Snehasis Patnaik

      Hi there -

       

      I went through few discussions on this title but nothing helped hence creating a new one.

       

      I have data like this -

       

      Emp IdStart DateEnd Date

       

       

      I want an output like this -

       

      Month-YearCount of Employees

       

       

      *** I have start dates from 1992 but I have to build a report of employee count from Jan-2015 (Which will consider the count before 2015 as well).

       

      Thanks in advance

       

      -Sneh

        • Re: Employees HeadCount
          Kaushik Solanki

          Hi,

           

          You will have to use the interval match function.

           

          Regards,

          Kaushik Solanki

            • Re: Employees HeadCount
              Snehasis Patnaik

                                      

              Hi Thanks for the quick response .

               

              Below is the script I've written -


              Employee:

              Load *,
              if(not exists(Endtemp), today(), Endtemp) as [End Date]
              ;

              LOAD
              //'Employee' as Table_Name,
                Upper(REGION) as REGION,
              ANALYST_KEY,
              LOCAL_ID,
              ANALYST_NAME,

              START_DATE as [Start Date],
              Month(START_DATE) as Start_Month,
              Year(START_DATE) as Start_Year,
              END_DATE as Endtemp,
              Month(END_DATE) as End_Month,
              Year(END_DATE) as End_Year

              FROM
              ANALYST.qvd
              (
              qvd);

              Calendar:
              Load
              Date,
              Year(Date) & '-' & Month(Date) as Yyyymm,
              Month(Date) as Month,
              Year(Date) as Year;
              Load
              Date(MinDate+IterNo()) as Date
              while MaxDate - MinDate >= IterNo()
              ;
              LOAD
              MIN([Start Date]) as MinDate,
              Max([End Date]) as MaxDate
              Resident Employee;

              Interval:
              Inner join (Employee)
              intervalMatch (Date)
              Load distinct [Start Date], [End Date]
              resident Employee
              ;

              In UI table I'm ng Yyyymm as dimension and Count(Distinct Analyst_Key) as Expression.

               

              and the numbers don't seem to match with the actuals numbers.

               

              Where m I wrong?

            • Re: Employees HeadCount
              Sunny Talwar

              Use Loops in the Script (while loop) to create a single date field from a date range or you can use an IntervalMatch to do this.

                • Re: Employees HeadCount
                  Snehasis Patnaik

                  Hi Thanks for the quick response .

                   

                  Below is the script I've written -


                  Employee:

                  Load *,
                  if(not exists(Endtemp), today(), Endtemp) as [End Date]
                  ;

                  LOAD
                  //'Employee' as Table_Name,
                    Upper(REGION) as REGION,
                  ANALYST_KEY,
                  LOCAL_ID,
                  ANALYST_NAME,

                  START_DATE as [Start Date],
                  Month(START_DATE) as Start_Month,
                  Year(START_DATE) as Start_Year,
                  END_DATE as Endtemp,
                  Month(END_DATE) as End_Month,
                  Year(END_DATE) as End_Year

                  FROM
                  ANALYST.qvd
                  (
                  qvd);

                  Calendar:
                  Load
                  Date,
                  Year(Date) & '-' & Month(Date) as Yyyymm,
                  Month(Date) as Month,
                  Year(Date) as Year;
                  Load
                  Date(MinDate+IterNo()) as Date
                  while MaxDate - MinDate >= IterNo()
                  ;
                  LOAD
                  MIN([Start Date]) as MinDate,
                  Max([End Date]) as MaxDate
                  Resident Employee;

                  Interval:
                  Inner join (Employee)
                  intervalMatch (Date)
                  Load distinct [Start Date], [End Date]
                  resident Employee
                  ;

                  In UI table I'm ng Yyyymm as dimension and Count(Distinct Analyst_Key) as Expression.

                   

                  and the numbers don't seem to match with the actuals numbers.

                   

                  Where m I wrong?

                   

                   

                • Re: Employees HeadCount
                  Anil Babu Samineni

                  First you need to make it as single date

                   

                  Load EmpId, Date(StartDate + IterNo() - 1) as FinalDate

                  While Date(StartDate + IterNo() - 1) <= EndDate;

                  Load EmpId, StartDate, EndDate;


                  Then create Straight table like

                  Date(FinalDate,'MMM-YYYY') as Calculated Dimension and label called MonthYear

                  Expression is Count({<Date = {">=$(=MakeDate(2015, 01, 01)) <=$(=Max(Date))"}>}EmpId)

                  • Re: Employees HeadCount
                    Arvind Patil

                    Dear Snehasis,

                     

                    It may help you.

                     

                    //SET DateFormat='DD.MM.YYYY';

                     

                    EmpData:

                    Load * INLINE [

                    EmpID, Leave, Date Start, Date End

                    100, Leave, 01.01.2017, 10.01.2017

                    100, Leave, 15.01.2017, 20.01.2017

                    101, Leave, 10.01.2017, 20.01.2017

                    102, Leave, 18.01.2017, 30.01.2017

                    103, Leave, 25.01.2017, 31.01.2017

                    105, Leave, 30.01.2017, 31.01.2017

                    ];

                     

                    Final:

                    LOAD [EmpID], [Leave], [Date Start] as Sdate , [Date End] as Edate,

                    Date([Date Start]+iterno()-1) as Date

                    resident EmpData while [Date Start]+IterNo()-1 <=[Date End];

                     

                    drop table EmpData;

                     

                     

                     

                    Thanks ,

                    Arvind Patil