topic Re: Period count in QlikView

Period count

piekepotloed — Wed, 24 Sep 2014 13:47:27 GMT

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...

Re: Period count

Anonymous — Wed, 24 Sep 2014 13:50:09 GMT

Hi,

intervalmatch function can sort you out here.

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

rgds,

Artur

Re: Period count

rubenmarin — Wed, 24 Sep 2014 13:59:24 GMT

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

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

and a expression:

Count(Distinct Employer)

Re: Period count

demoustier — Wed, 24 Sep 2014 14:06:05 GMT

something like attached file ?

Re: Period count

piekepotloed — Thu, 25 Sep 2014 20:56:00 GMT

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

Re: Period count

MarcoWedel — Thu, 25 Sep 2014 23:43:25 GMT

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

Re: Period count

Fri, 26 Sep 2014 00:14:20 GMT

Hi,

I would do it in a straight table.

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

Expression:- COUNT (DISTINCT Employer)

Cheers

Prasan

Re: Re: Period count

demoustier — Fri, 26 Sep 2014 07:05:43 GMT

I try again 🙂

Re: Period count

Anonymous — Thu, 24 Mar 2016 04:59:06 GMT

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