# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for
Did you mean:
Not applicable

## Employee headcount based date of resignation

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.

11 Replies
Partner

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)

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

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 )

talk is cheap, supply exceeds demand
Not applicable
Author

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

Partner

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP

use intervalmatch to link to a calendar table.

regards

Marco

Not applicable
Author

Hi all,

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

MVP

one example:

```tabEmpDates:
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:
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;
While MinDate+IterNo()-1 <= Today();
Resident tabEmpDates;

IntervalMatch (Date)
Resident tabEmpDates;
```

hope this helps

regards

Marco

Not applicable
Author

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

MVP

maybe you don't have to:

Should We Stop Worrying and Love the Synthetic Key?

regards

Marco

Community Browser