Period count

[piekepotloed]

Employer	Start date	End date
A	1-8-2012
B	1-7-2014
C	1-5-2013	31-12-2013
D	1-6-2014
E	1-9-2013	31-8-2014
F	1-12-2013
G	1-5-2014

This is my staff table, I want to achieve this

Month	Number of people hired
2014-09	5
2014-08	6
2014-07	6
2014-06	5
2014-05	4
2014-04	3
2014-03	3
2014-02	3
2014-01	3
2013-12	4
2013-11	4
2013-10	4

How can I achieve this? Do I need to create 12 variables or...

[Anonymous]

Hi,

intervalmatch function can sort you out here.

a good example can be found here: Interval Match Feature/Function

rgds,

Artur

[rubenmarin]

Hi Peter, you can create a table with a calculated dimension like:

Date([Start date], 'YYYY-MM')

and a expression:

Count(Distinct Employer)

[demoustier]

something like attached file ?

[piekepotloed]

I'm sorry, Demoustier, but there is no file...

[MarcoWedel]

Hi,

one solution could be:

tabEmployees:
LOAD *,
    AutoNumberHash128(MonthStart, MonthEnd) as %Key;
LOAD *,
    MonthStart([Start date]) as MonthStart,
    MonthEnd(Alt(Num([End date]),Today())) as MonthEnd
FROM [http://community.qlik.com/thread/134805] (html, codepage is 1252, embedded labels, table is @1);

tabCalendar:
LOAD Date(AddMonths(MinMonth, IterNo()-1),'YYYY-MM') as Month
While AddMonths(MinMonth, IterNo()-1) <= MonthName(Today());
LOAD MonthName(Min([Start date])) as MinMonth
Resident tabEmployees;

tabLink:
IntervalMatch (Month)
LOAD MonthStart, MonthEnd
Resident tabEmployees;

Left Join (tabLink)
LOAD Distinct
  MonthStart,
  MonthEnd,
  AutoNumberHash128(MonthStart, MonthEnd) as %Key
Resident tabLink;

DROP Fields MonthStart, MonthEnd From tabLink;

hope this helps

regards

Marco

[#7]

Hi,

I would do it in a straight table.

Dimension:- Date(MonthEndDate(StartDate),'YYYY-MM')

Expression:- COUNT (DISTINCT Employer)

Cheers

Prasan

[demoustier]

I try again 🙂

[Anonymous]

Hi Marco,

I wish to display past 12 months data.

Which date do we use to create a master calendar in this case where we have intervals?

Thanks for your help!

Regards,

Nelson