Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Counting "active" records between two dates

I have a data-set that consists of one main table, and each record has a start and end date.  What I'm looking to do is graph a date dimension, say years, against a measure of how many records are active during that period.  That is to say, for each year count the number of records with a start date less than the year and with an end date greater than or equal to the date.

Since it's always nice to look at a data-set, there's a great data-set for baseball players here: http://www.seanlahman.com/baseball-archive/statistics/

Let's approximate active players for any year by measuring "debut" and "finalGame" from the "Master.csv" in that package.

If I were to try and solve this in another tool, say a map-reduce framework, I would produce a list of years from every record, then add up all of the records for the same year.  For example take:

IDdebutfinalGame
aaroh1014/13/195410/3/1976
abert1014/13/19559/30/1972
acket1014/20/19569/20/1959

Which would turn into a table like this (cut off at 1960 for brevity)

YearActive Players
19541
19552
19563
19573
19583
19593
19602

Using Qlik Sense, I'm guessing there are two approaches:

  1. Create a separate table in the load script
  2. Somehow use set expressions when setting up a chart

I'd be open to either approach, but I'm afraid I don't quite know enough about Qlik to implement either one.

Any suggestions would be appreciated.

Tags (2)
1 Reply
MVP
MVP

Re: Counting "active" records between two dates

Quite interesting in fact - I first did it in QlikView and then I reproduced it in Qlik Sense.

Both the approaches you mention is possible and the Qlik Sense Application I have attached will show how do do it in either way - Load Script and entirely in the UI.

2015-05-05 #1.PNG

2015-05-05 #2.PNG

2015-05-05 #3.PNG

Community Browser