Accumulation in Script

[#1]

Hi,

I have a table with expenses and dates.

How can I accumulate the data by months?

For exemple we have a table with the data:

dd/mm/yyyy	expenses
01/01/2010	50
02/01/2010	55
15/01/2010	60
20/01/2010	45
03/02/2010	15
27/02/2010	55
09/03/2010	70
18/03/2010	30

If I select 'Jan' I get 210, If I select 'Feb' i get 70, If I select 'Mar' I get 100.

What i want is, if I select 'Jan' I get 210, If I select 'Feb' i get 280, If I select 'Mar' I get 380.

How can we do this in QV in the Script?

Thank you.

[Miguel_Angel_Baeyens]

Hello Melisa,

If the data is as simple as that (one record per month) then yes, the script can be simplified to the following (again, dummy data, may make no sense)

Table:LOAD IterNo() AS Month, Ceil(Rand() * 1000) AS Monthly // Amount per monthAUTOGENERATE 1 WHILE IterNo() < 13; TableStep2: // MonthAcum is current month + previousLOAD *, RangeSum(Monthly, Previous(Monthly)) AS MonthAcum // RangeSum will sum NULL (first record) as zero while + will return NULL RESIDENT TableORDER BY Month; DROP TABLE Table; AcumTable: // Amount is the sum of all MonthAcumLOAD *, RangeSum(MonthAcum, Peek('Amount')) AS AmountRESIDENT TableStep2; DROP TABLE TableStep2;

It needs to be done in three steps because of the Rand(), in your table all steps can be done in the same table.

Hope that helps.