10 Replies Latest reply: Apr 12, 2011 5:31 AM by Miguel Angel Baeyens de Arce RSS

    Accumulation in Script


      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:



      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.

        • Accumulation in Script



          You can achieve the same by using peek() statement.



          Thanks & Regards

            • Accumulation in Script

              Thank you,

              But could you please sow this expression in an example?

                • Accumulation in Script


                  Please find the attached app, hope this will help you.



                  Thanks & Regards


                    • Accumulation in Script

                      Hi, Upendra11

                      Thank you a lot. I got what the peek() expression serves for.

                      Sorry, but in this exemple date is always a column. But in my practice I may use the month(date) expression and use it as row (because there are a lot of other fields (account, unit, function) to be presented in columns)... May be there is som other way out to accumulate the data on the date basis?

                      Besides, I just remembered that I have a file, where vice versa I have accumulated data. For example, I get general ledger for January with transactions made in January. In February I get another General Ledger with tranasction from January + Februray. Accumulated. But I need a nonaccumulated data. So from the Feb's Gen Ledger I select the transaction I already had in Janury. May be there is an easier way to do this?

                        • Accumulation in Script
                          Miguel Angel Baeyens de Arce

                          Hello Melisa,

                          Check this script as an example of getting month accumulation:


                          Table:LOAD Chr(64 + Ceil(Rand() * 2)) AS Code, Month(Date('01/01/2011') + Ceil(Rand() * 90)) AS Month, Ceil(Rand() * 1000) AS AmountAUTOGENERATE 10; TableStep2:NOCONCATENATE LOAD *RESIDENT TableORDER BY Code, Month; DROP TABLE Table; AcumTable:LOAD *, If(Code = Previous(Code), If(Month = Previous(Month), RangeSum(Amount, Peek('MonthAcum')), Amount), Amount) AS MonthAcumRESIDENT TableStep2; DROP TABLE TableStep2;

                          In the other case -I may have missed something- how can you non accumulate data? I mean, if you have data only for January, is there any field less than month you can use to divide your amounts into (date, week)? Otherwise, how would you split that amount?

                          Hope that helps.

                            • Accumulation in Script

                              Hi, Miguel.

                              I need some time to understand the expressions you use, so I can tell if it helps only tomorrow. But thank you a lot fir your answer!

                              Speaking about the another case, ofcourse in January we have the data only for Janury. But in Februry we have for both for Jan and for Feb, so we need to make Feb-Jan=the data only fo Feb. But it is very unconvinient for the next 12 Months

                                • Re: Accumulation in Script
                                  Miguel Angel Baeyens de Arce

                                  Hello Melisa,

                                  Using the same Previous() and Peek() functions you can do something similar, but instead of adding, substracting this current month's amount to previous month amount


                                  LOAD IterNo() AS Month, 
                                       Ceil(Rand() * 1000) AS Amount
                                  AUTOGENERATE 1 WHILE IterNo() < 13; 
                                  NOCONCATENATE LOAD *
                                  RESIDENT Table
                                  ORDER BY Month; 
                                  DROP TABLE Table; 
                                  LOAD *, 
                                       If(Month > Previous(Month), RangeSum(Amount, Peek('MonthAcum') * -1), Amount) AS MonthAcum
                                  RESIDENT TableStep2; 
                                  DROP TABLE TableStep2;


                                  The idea is that Amount in the first table has the value for this and previous months (which in the example above is not correct, since amounts are randomized) and if that the current record's month is greater than previous record, subtract to this month's amount, previous' amount.

                                  With more sensible data, I think it's worth trying.

                                  Hope that helps.

                                    • Accumulation in Script

                                      Hi, Miguel.

                                      Thank you very much, yes that works.

                                      But here we have


                                      And what i need is



                                      May be that is even easier...? Can we do this with the help of PEEK()?

                                        • Accumulation in Script
                                          Tresesco B

                                          Hi Melisa,

                                          Yes, you can do it with peek. As Miguel suggested (it might need a basic modification, but the logic is surely fine.). you can find one of my post helpful:


                                          Regards, tresesco

                                          • Accumulation in Script
                                            Miguel Angel Baeyens de Arce

                                            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.