Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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 )
What would be the variable vTestDate from where it is taking the date..Can you please tell me
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.
use intervalmatch to link to a calendar table.
regards
Marco
Hi all,
I am new to qlikview ..please give me some example QVW file to work on my side
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
Thanks Margo Its really helped me lot but we are getting synthetic key and how to avoid