15 Replies Latest reply: May 14, 2012 5:55 AM by João Franco RSS

    Head Count

    João Franco

      Hi, i need to count the number of active of employees in the company, so i need to exclude the employees who have left the company....

      so i know the active employees have the type contract ("101","102","103","104","105","106","107","108"), but my problem is that every employee has records for every month and year, even if they become inactive.

      I have NumberEmployee, AdmissionDate, DemissionDate, YearEmployee (every year since he has been employed, until the present year), MonthEmploye (every month since he has been employed, until the present month), TypeContract and the YearMonthEmployee.

      I think my problem is that i can't select the last record (row) of the employee.... i'm counting employees as being  active when in fact they are already inactive, and vice-versa.


      i'm using the following expression:

       

      =sum({<TypeContract={"101","102","103","104","105","106","107","108"},YearEmployer={"$(=only(Year))"}>}
             aggr(
                     if(
                        max({< YearEmployer = {"$(=only(Year))"}>}YearMOunthEmployer
                        and(
                                    DemissionDate >= vMinDateCalendar
                                     or
                                    LEN( DemissionDate) = 0
                             )
                             ,1
                     )
             ,NumEmployer)
             )
         
         
         
      do you have any idea of what might be wrong ??

      ty reggards

        • Head Count
          Marc Livingston

          I dont know if it will help, but I do something similar with this:

           

          Count(Distinct (if([Calendar Date]>=[Hire Date] and [Calendar Date]<= if(Status='Active',[Active Date],[Inactive Date]) and [Calendar Date]<=today(),Employee)))

           

          This method for me will show them as active until their inactive date and if they do not have an inactive date i calculate today()*2 to put a future date in.

          • Head Count
            Celambarasan Adhimulam

            Hi,

                 Its better to make a flag isActive (means field) in the loadscript by comparing with the AdmissionDate, DemissionDate.

                 I mean if the date is between AdmissionDate and DemissionDate then 1 else 0.

            Ex:

                 Table:

                 Load

                      *,

                 if(DateField>=AdmissionDate and DateField<=DemissionDate , 1,0) as isActive

                 From Datasource;

            Then use set analysis as

                 Sum({<TypeContract={"101","102","103","104","105","106","107","108"},isActive={1}>} FieldName)

             

            Celambarasan

              • Re: Head Count
                João Franco

                Hello,

                 

                Celambarasan ty.

                 

                ok that's correct, and i did that.... i forget to mention that....

                 

                but what i get from that is

                 

                 

                NumEmploy Month Year Active?

                001                 04    2010  S

                001                 05    2010  S

                001                 06    2010  N

                001                 07    2010  N

                 

                and what i want is to retrieve the last row of the Employ 001  and know he's not active any more.....

                 

                 

                do u understand ?

                 

                ty again.....

                  • Head Count
                    Celambarasan Adhimulam

                    Hi,

                         Then try this

                         After doing the above approach

                         CurrentStatus:

                         Load

                              NumEmployee,

                              LastValue(Active) as CurrentlyActive

                         Resident

                              TableName

                         Group By NumEmployee;//Previously Loaded TableName

                     

                    Celambarasan

                      • Head Count
                        João Franco

                        Celambarasan, this is great !!

                         

                        i created the lastvalue(active) and the lasvalue(typecontract)

                         

                         

                        and it's works, but !! there's always a but when i select the 2011 year, it doesnt count the employees who have been fired in 2012...

                         

                        Because it only takes into account the last record status values...  how can i make this work with the calendar witohout having to connect this table with the calendar itself?

                         

                         

                        apreciate !! ty !

                         

                          • Head Count
                            João Franco

                            anybody can help ?

                            • Head Count
                              Celambarasan Adhimulam

                              Hi,

                                   You mean you need to count the employees fired in 2012 on selecting 2011 for active employees?

                               

                              Celambarasan

                                • Re: Head Count
                                  João Franco

                                  what i mean is that i need to count the employees active in 2011 on selecting 2011.

                                  What i need is to be able to count the number of active employees on a determined date. For example, if i select 2011, i need to see how many employees were active until the end of 2011. If i select 2012, get the number of employees that are still active to this date.

                                  What the function lastvalue() does is retrieve the last known status of each employee, i.e. the last record's status. That does not help me because when i select 2011, an employee who got fired in 2012 appears as inactive. 

                                  In short, i need the lastvalue() to retrieve the last record in accordance to the select calendar year.

                                   

                                  can understand ?

                                    • Re: Head Count
                                      Celambarasan Adhimulam

                                      Hi,

                                           In that case this methd itself will help know?

                                      Table:

                                           Load

                                                *,

                                           if(DateField>=AdmissionDate and DateField<=DemissionDate , 1,0) as isActive

                                           From Datasource;

                                      Then use set analysis as

                                           Sum({<TypeContract={"101","102","103","104","105","106","107","108"},isActive={1},YearEmployer={"=$(=Max(Year))"}>} FieldName)

                                       

                                      Celambarasan

                                        • Re: Head Count
                                          João Franco

                                          no :| my datefield isn't static... i need to choose beetwen 2009 and 2012.... if i do that in script how he will detect when i'm changing the year ?

                                            • Re: Head Count
                                              Celambarasan Adhimulam

                                              Hi,

                                                   For that only you are using this to get the selected year

                                              YearEmployer={"=$(=Max(Year))"}

                                               

                                              Celambarasan


                                                • Re: Head Count
                                                  João Franco

                                                  Bahh now i can do it... !! the expression is like that.....

                                                   

                                                   

                                                  =count({<AdmissionDate={"<=$(vMaxDateCalendar)"},CheckActive={"Active"},ContractType={101,102,103,104,105,106,107,108}>} DISTINCT

                                                  if(

                                                  aggr( nodistinct max(YearMounth), EmployerNumber) = YearMounth

                                                  ,

                                                  EmployerNumber

                                                  )

                                                  )

                                                   

                                                   

                                                  ok.... now this is working..... BUT when i make in the set analysis

                                                   

                                                  YearEmployer={"=$(=Max(Year))"} it doesn't nothing, even  i make (Year)-1   ..... nothing....

                                                   

                                                  and when i make

                                                   

                                                  YEAR={"$(=only(Year))"}

                                                   

                                                  it's ok.... perfect... BUT when i make the last year.... i make

                                                   

                                                  YEAR={"$(=only(Year)-1)"}  or YEAR={"$(=only(Year-1))"}

                                                   

                                                   

                                                  and that makes the expression on zero....  "0" :| can't understand why...

                                                   

                                                   

                                                  anybody can help me ?!  i apreciate.......

                                                   

                                                   

                                                  [   ]

                                • Head Count
                                  qliksus

                                  Your expression syntax is wrong

                                   

                                   

                                   

                                  if(

                                  max({< YearEmployer = {"$(=only(Year))"}>}YearMOunthEmployer

                                  and( DemissionDate >= vMinDateCalendar or LEN( DemissionDate) = 0 ) ,1 ) ,NumEmployer )

                                   

                                  here you have not specified what u want to take in the Max function and aggr is created

                                   

                                  without any domension so correct that to get some results

                                    • Head Count
                                      João Franco

                                      Qliksus ty...

                                       

                                      i don't understand what u want to mean....

                                       

                                      i made that with ur answer.....

                                       

                                      max({< YearEmployer = {"$(=only(Year))"}>}YearMOunthEmployer)

                                                        and(

                                                                    DemissionDate >= vMinDateCalendar

                                                                     or

                                                                    LEN( DemissionDate) = 0

                                                             )

                                                             ,1

                                                     )

                                             ,NumEmployer)

                                       

                                       

                                       

                                      the aggr it's not the NumEmployer to get dimension ?!

                                       

                                      ty