topic Re: Head Count in QlikView

Head Count

pkpandey — Thu, 06 Oct 2016 13:01:02 GMT

Hi All

I have to make a bar chart Month wise and Area of Work wise to show the no of employee. For example if an employee has joined in the month of April'2015. It should show the count from April'2015 to Dec'2015 as 1 and also in the year 2016 as 1 from Jan'2016 to Dec'2016 for that DEPARTMENT.

Further if an employee has resigned in the month of May'2015, it should show the count as 1 from Jan'2015 to May'2015 and from jun'2015 to Dec'2015 as 0 for the same DEPARTMENT.

Sample data of excel sheet and qvw are also attached.

Re: Head Count

sunny_talwar — Thu, 06 Oct 2016 13:17:39 GMT

Does this look like what you want?

Script:

Report:

LOAD

Employee,

[Area Of Work],

Date As fDate,

Remarks

FROM

[Sample.xlsx]

(ooxml, embedded labels, table is [Head Count]);

FinalReport:

LOAD Employee,

[Area Of Work],

fDate as Date,

MonthStart(fDate, IterNo()-1) as fDate,

Remarks

Resident Report

While MonthStart(fDate, IterNo()-1) < MakeDate(Year(fDate)+1, 1, 1) and Remarks = 'Joined';

Concatenate (FinalReport)

LOAD Employee,

[Area Of Work],

fDate as Date,

MonthStart(fDate, -IterNo()+1) as fDate,

Remarks

Resident Report

While MonthStart(fDate, -IterNo()+1) >= MakeDate(Year(fDate), 1, 1) and Remarks = 'Resigned';

DROP Table Report;

Re: Head Count

pkpandey — Fri, 07 Oct 2016 05:32:04 GMT

Hi Sunny

Thank you for your quick response. But here the count for accounts and sales also show for the year 2016 and Audit count should show also for the year 2015.

Re: Head Count

sunny_talwar — Fri, 07 Oct 2016 07:23:18 GMT

Not sure I understand completely, but the above snapshot was taken when Year 2015 was selected. Here is snapshot when I have not selected any year. Does this look right?

Re: Head Count

pkpandey — Thu, 13 Oct 2016 09:42:34 GMT

Thanks Sunny

It was very helpful.

Re: Head Count

sunny_talwar — Thu, 13 Oct 2016 09:44:34 GMT

Cool, if your issue is now resolved, I would suggest you to close this thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Head Count

MarcoWedel — Fri, 14 Oct 2016 22:29:58 GMT

Hi,

one solution might be also:

Report:
LOAD Employee,
  [Area Of Work],
  Date,
  Remarks
FROM [https://community.qlik.com/servlet/JiveServlet/download/1137673-248513/Sample.xlsx] (ooxml, embedded labels, table is [Head Count])
Where Len(Employee);

tabEmpDates:
LOAD Employee,
    Date(Alt(Min(If(Remarks='Joined',Date)),MakeDate(2015))) as Joined,
    Date(Alt(Max(If(Remarks='Resigned',Date)),Floor(YearEnd(Today())))) as Resigned
Resident Report
Group By Employee;

tabCalendar:
LOAD fDate,
    Week(fDate) as Week,
    Year(fDate) as EYear,
    Month(fDate) as Month,
    Day(fDate) as Day,
    YeartoDate(fDate)*-1 as CurYTDFlag,
    YeartoDate(fDate,-1)*-1 as LastYTDFlag,
    InYear(fDate, MonthStart(MaxDate),-1) as RC12,
    Date(MonthStart(fDate), 'MMM-YYYY') as MonthYear,
    Dual('Q'&Ceil(Month(fDate)/3),Ceil(Month(fDate)/3)) as Quarter,
    Dual(Week(fDate)&'-'&WeekYear(fDate),WeekStart(fDate)) as WeekYear,
    WeekDay(fDate) as WeekDay,
    Num(Month(fDate)) As MonthOrder;
LOAD Date(MinDate+IterNo()-1) as fDate,
    MaxDate
While MinDate+IterNo()-1 <= MaxDate;
LOAD Min(Date) as MinDate,
    Floor(YearEnd(Today())) as MaxDate
Resident Report;

tabDateLink:
IntervalMatch (fDate)
LOAD Distinct
    Joined,
    Resigned
Resident tabEmpDates;

hope this helps

regards

Marco

Re: Head Count

MarcoWedel — Fri, 14 Oct 2016 22:40:18 GMT

maybe also helpful:

How to get employee count for missing years entry

regards

Marco