Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Tags (2)
15 Replies
Not applicable

Head Count

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

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

qliksus
Valued Contributor

Head Count

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

Not applicable

Re: Head Count

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

Not applicable

Head Count

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

Head Count

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

Not applicable

Head Count

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 !

Not applicable

Head Count

anybody can help ?

Head Count

Hi,

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

Celambarasan

Community Browser