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:
| ID | debut | finalGame |
|---|
| aaroh101 | 4/13/1954 | 10/3/1976 |
| abert101 | 4/13/1955 | 9/30/1972 |
| acket101 | 4/20/1956 | 9/20/1959 |
Which would turn into a table like this (cut off at 1960 for brevity)
| Year | Active Players |
|---|
| 1954 | 1 |
| 1955 | 2 |
| 1956 | 3 |
| 1957 | 3 |
| 1958 | 3 |
| 1959 | 3 |
| 1960 | 2 |
Using Qlik Sense, I'm guessing there are two approaches:
- Create a separate table in the load script
- 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.
