Qlik Community

Ask a Question

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 
Search instead 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
jonathandienst
Partner
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)

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
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 )


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

jonathandienst
Partner
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
MarcoWedel

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

MarcoWedel

one example:

QlikCommunity_Thread_169689_Pic1.JPG

QlikCommunity_Thread_169689_Pic2.JPG

QlikCommunity_Thread_169689_Pic3.JPG

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

Not applicable
Author

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