0 Replies Latest reply: Sep 2, 2015 7:35 AM by Hans de Vries RSS

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

    Hans de Vries

       

      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!