Line Chart function with dates as marginal conditions
Hello.
I have two tables, one with the columns 'Person', 'Date1', 'Date2','Coach' and 'Program' with the values of Date1 <= Date2.
The second table consists of just one column with the daily dates from '01.01.2000' to 31.12.2099' per row.
What I would like to do is build a chart that shows the number of persons from 'Person' grouped by coaches from 'Coach' (and optional by programs from 'Program' ) for every day of a given period of time.The person should just be counted if the day the value is calculated lies between Date1 and Date2.
For example Coach A has 10 Persons on date A (that is 10 lines in the data set with that coach on that day)
and 20 on date B and 30 on date C.
Is there a function/formula that allows to produce a line chart that shows the numer of persons per coach per day with selectable dates A and C ?
I have an idea for a rather brute force attack on the problem by precalculating the values for every coach and day in SQL Server and putting them in a data warehouse table, but would like to know if there is a possible solution in Qlikview.
