Employee headcount based date of resignation

[#1]

Emp    DOJ                 DOR

10     1-Apr-2012       13-June-2014

11     11-Apr-2013      15-Apr-2014

113    12-Apr-2011    

16     11-Nov-2014

12     14-Aug-2013      13-Nov-2014

I have an employee table information.I want employee count based on Day Selection.

Suppose if i have selected 15-Apr-2014 then all the employee count should come (employee count -5)

If I have selected 16-Apr-2014 then we need to exclude Employee ID 12 from the employee count (Emp count 4)

Because 15-Apr-2014 is last day for that employee so next day he should not come in the emplyee count.

Please anyone help me to give solution..Urgent help required.

[jonathandienst]

If you convert the null DOR values into a date far into the future (in the load script), this is quite simple if you are getting the value for a single date:

Count({<DOJ = {"<=$(=Date(vTestDate))"}, DOR = {">=$(=Date(vTestDate))"}>} Distinct Emp)

(to compare to a single date)

With load script:

LOAD ...

     DOJ,

     If(Len(DOR) = 0, '2999/12/31', DOR) As DOR,

     If(Len(DOR) = 0, 0, 1) As CurrentFlag,

     ...

This will not work in a table with date as a dimension. In a table with the field Date as a dimension, use

Count(Distinct If((DOJ <= Date) And (DOR >= Date Or Len(DOR) = 0), Emp))

As long as your data set is small, this will work - but if the set is large, this calculation may be slow. If this is the case, you may need to do some transformations in the load script using interval match to produce an employee-date link)

[Gysbert_Wassenaar]

You can use a Calendar object with a variable vSelectedDate to select a day and use something like this expression to calculate the headcount:

sum( i( DOJ < $(vSelectedDate) AND rangemin(DOR,today()) > $(vSelectedDate) ,1, 0 )

[#4]

What would be the variable vTestDate from where it is taking the date..Can you please tell me

[jonathandienst]

Its whatever you want it to be, from an input box or a calender object - that was an example to show how to get an employee count at a user-selected date. It probably needs to be adapted to your precise requirement which you did not state in your question.

[MarcoWedel]

use intervalmatch to link to a calendar table.

regards

Marco

[#7]

Hi all,

I am new to qlikview ..please give me some example QVW file to work on my side

[MarcoWedel]

one example:

tabEmpDates:
LOAD Emp,
    DOJ,
    If(Len(DOR),DOR, Dual('current Emp',MakeDate(2099))) as DOR
INLINE [
    Emp, DOJ, DOR
    10, 1-Apr-2012, 13-Jun-2014
    11, 11-Apr-2013, 15-Apr-2014
    113, 12-Apr-2011
    16, 11-Nov-2014
    12, 14-Aug-2013, 13-Nov-2014
];

tabCalendar: 
LOAD *, 
    Day(Date) as Day, 
    WeekDay(Date) as WeekDay, 
    Week(Date) as Week, 
    WeekName(Date) as WeekName, 
    Month(Date) as Month, 
    MonthName(Date) as MonthName, 
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
    QuarterName(Date) as QuarterName, 
    Year(Date) as Year, 
    WeekYear(Date) as WeekYear;   
LOAD Date(MinDate+IterNo()-1) as Date 
While MinDate+IterNo()-1 <= Today(); 
LOAD Min(DOJ) as MinDate
Resident tabEmpDates;

tabLink:
IntervalMatch (Date)
LOAD DOJ, DOR
Resident tabEmpDates;

hope this helps

regards

Marco

[#9]

Thanks Margo Its really helped me lot but we are getting synthetic key and how to avoid

[MarcoWedel]

maybe you don't have to:

Should We Stop Worrying and Love the Synthetic Key?

regards

Marco