Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
piekepotloed
New Contributor

Period count

EmployerStart dateEnd date
A

1-8-2012

B1-7-2014
C1-5-201331-12-2013
D1-6-2014
E1-9-201331-8-2014
F1-12-2013
G1-5-2014

This is my staff table, I want to achieve this

Month

Number of people hired

2014-095
2014-086
2014-076
2014-065
2014-054
2014-043
2014-033
2014-023
2014-013
2013-124
2013-114
2013-104

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

8 Replies
arturkaluza
New Contributor III

Re: Period count

Hi,

intervalmatch function can sort you out here.

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

rgds,

Artur

Re: Period count

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

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

and a expression:

Count(Distinct Employer)

demoustier
Contributor

Re: Period count

something like attached file ?

piekepotloed
New Contributor

Re: Period count

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

Re: Period count

Hi,

one solution could be:

QlikCommunity_Thread_134805_Pic1.JPG.jpg

QlikCommunity_Thread_134805_Pic2.JPG.jpg

QlikCommunity_Thread_134805_Pic3.JPG.jpg

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

Not applicable

Re: Period count

Hi,

I would do it in a straight table.

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

Expression:- COUNT (DISTINCT Employer)

Cheers

Prasan

demoustier
Contributor

Re: Re: Period count

I try again 🙂

a5123283
New Contributor III

Re: Period count

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