Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Head Count

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

15 Replies
Not applicable
Author

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 ?

CELAMBARASAN
Partner - Champion
Partner - Champion

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

Not applicable
Author

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 ?

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

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

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

Celambarasan


Not applicable
Author

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

[   ]

Not applicable
Author

nothing .... ?!