1 Reply Latest reply: May 5, 2015 5:37 PM by Petter Skjolden RSS

    Counting "active" records between two dates

    Chris Scott

      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:




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


      YearActive Players


      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.