How do I create a table from variables to generate totals per month?

hansdevr — Wed, 02 Sep 2015 11:35:35 GMT

I have the following table (simplified):

ID	Start_date	End_date	Amount_In_€
10001	1-6-2015	4-6-2015	5000
10001	5-6-2015	9-6-2015	4000
10001	10-6-2015	31-8-2015	2000
20001	1-4-2015	3-6-2015	7500
20001	4-6-2015	1-7-2015	4000
20001	2-7-2015	31-8-2015	0
30001	1-3-2015	17-3-2015	10000
30001	18-3-2015	11-4-2015	6200
30001	12-4-2015	31-8-2015	4000

Using this expression in a text box, allows me to sum the amounts for any given date (vDate) over the entire range of records in my table (imagine many thousands of ID’s).

=Money(sum( if( Start_date<= vDate AND End_date>= vDate, [Amount_in_€]) ))

(which means that only a portion of the table is totalized, i.e. the records that meet the expression)

Now, what I’d like to do, is show the progression of the total from the expression over a range of dates (oldest Start_date up to today) in a line graph.

Thinking about this, I guess I will have to generate a new table, that would look something like this:

Date	Total_in_€
1-4-2015	2.654.290
2-4-2015	22.380.411
3-4-2015	23.015.766
4-4-2015	24.844.321

Could anyone help me make the script that builds this new table from the original one?

Many thanks in advance!

topic How do I create a table from variables to generate totals per month? in QlikView

How do I create a table from variables to generate totals per month?