Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.