Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
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.....
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
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
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 !
anybody can help ?
Hi,
You mean you need to count the employees fired in 2012 on selecting 2011 for active employees?
Celambarasan