topic Census date headcount in QlikView

Census date headcount

Mon, 10 Feb 2014 09:57:58 GMT

Hi,

wondered is anyone can help point me in the right direction with this,

I have a set data which list all posts (jobs) in an organisation since 2008 and the post holder employed in each, within this is included two fields

post start date

post leaving date (be blank if the person is still in post)

I want to be able have a selection list of the census dates (e.g. 1st April 2008,1 April 2009,1 April 2010, 1 April 2011 etc) and use this to filter the graphs/tables as to who was employed on the census date selected.

the logic of filtering who is employed from these two dates would be:

if 'post start date' is less than or equal to [census date] and 'post leaving date' is more than or equal to [census date] or is null

Would this be something feasible from an expression in the front end or would something need to be generated in the load?

Any ideas appreciated.

Thanks

Re: Census date headcount

Nicole-Smith — Mon, 10 Feb 2014 14:46:25 GMT

You'll need to use interval match in the load script, something like this:

Jobs:

LOAD * INLINE [

Employee,Start Date,End Date

John Smith,3/31/2008,4/2/2010

Jane Smith,4/1/2008,3/31/2009

Joe Smith,4/15/2008,

];

CensusDate:

LOAD * INLINE [

Census Date

4/1/2008

4/1/2009

4/1/2010

4/1/2011

];

IntervalMatch:

IntervalMatch([Census Date])

LOAD [Start Date], [End Date]

RESIDENT Jobs;

Do not try to get rid of the synthetic key that is created by the interval match--it is perfectly fine.

I have attached a working example.

Re: Census date headcount

Mon, 10 Feb 2014 16:48:58 GMT

Hi Nicole

many thanks for the example and tip on the load script, this is very helpful! I'm working on this approach now and is looking promising.

Best wishes

...Charles