Head Count

[pkpandey]

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.

[sunny_talwar]

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;

[pkpandey]

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.

[sunny_talwar]

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?

[pkpandey]

Thanks Sunny

It was very helpful.

[sunny_talwar]

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

[MarcoWedel]

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

[MarcoWedel]

maybe also helpful:

How to get employee count for missing years entry

regards

Marco