Employee Count for each month

[shivanandk]

Hi All,

I have the following data sample

Data:

LOAD * INLINE [

    Name, StartPeriod, EndPeriod

    John, 201501, 201511

    Sam,  201505, 201508

    Julie,  201504, 201510

];

I need to produce a report for employee count for each month

Period	EmployeCount
201501	1
201502	1
201503	1
201504	2
201505	3
201506	3
201507	3
201508	3
201509	2
201510	2
201511	1

How can I achieve this in QlikView.

Thank you,

SK

[#2]

Hi,

Please have a look into the attached qvw.

Hope this serves your purpose.

Regards,

Jemimah

[maxgro]

RESULT

Chart

Dimension     Period

Expression     Count(Name)

SCRIPT

Data:

LOAD

  Name,

  Date(Date#(StartPeriod & '01','YYYYMMDD'), 'YYYYMM') as StartPeriod,

  Date(Date#(EndPeriod & '01','YYYYMMDD'), 'YYYYMM')as EndPeriod

INLINE [

    Name, StartPeriod, EndPeriod

    John, 201501, 201511

    Sam,  201505, 201508

    Julie,  201504, 201510

];

DataByPeriod:

Load

  Name,

  StartPeriod, EndPeriod,

  AddMonths(StartPeriod, IterNo() -1) as Period

Resident Data

While AddMonths(StartPeriod, IterNo() -1) <= EndPeriod;

[petter]

Here you have a solution which is using IntervalMatch() which is very powerful and efficient:

[shivanandk]

Great ! Thank you for suggestion.

SK

[MarcoWedel]

Hi,

another solution could be:

Data:
LOAD Name,
     Date#(StartPeriod,'YYYYMM') as StartPeriod,
     Date#(EndPeriod,'YYYYMM') as EndPeriod,
     AutoNumberHash128(StartPeriod, EndPeriod) as %IntervalID    
INLINE [
    Name, StartPeriod, EndPeriod
    John, 201501, 201511
    Sam,  201505, 201508
    Julie,  201504, 201510
];

tabPeriods:
LOAD *,
     QuarterName(Period) as QuarterName,
     Year(Period) as Year;
LOAD Distinct
  %IntervalID,
  Date(AddMonths(StartPeriod,IterNo()-1),'YYYYMM') as Period
Resident Data
While AddMonths(StartPeriod,IterNo()-1)<=EndPeriod;

hope this helps

regards

Marco