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:

       

      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.