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!