topic Re: Employee Count for each month in QlikView

Employee Count for each month

shivanandk — Fri, 13 Nov 2015 23:14:39 GMT

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,

Re: Employee Count for each month

Sat, 14 Nov 2015 05:20:45 GMT

Hi,

Please have a look into the attached qvw.

Hope this serves your purpose.

Regards,

Jemimah

Re: Employee Count for each month

maxgro — Sat, 14 Nov 2015 07:55:24 GMT

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;

Re: Employee Count for each month

petter — Sat, 14 Nov 2015 08:08:03 GMT

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

Re: Employee Count for each month

shivanandk — Sat, 14 Nov 2015 14:09:35 GMT

Great ! Thank you for suggestion.

Re: Employee Count for each month

MarcoWedel — Sun, 15 Nov 2015 02:11:08 GMT

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