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

    Accumulation in Script

      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/yyyyexpenses
      01/01/201050
      02/01/201055
      15/01/201060
      20/01/201045
      03/02/201015
      27/02/201055
      09/03/201070
      18/03/201030


      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

          HI,

           

          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

                  Hi,

                  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

                                   

                                  Table:
                                  LOAD IterNo() AS Month, 
                                       Ceil(Rand() * 1000) AS Amount
                                  AUTOGENERATE 1 WHILE IterNo() < 13; 
                                  
                                  TableStep2:
                                  NOCONCATENATE LOAD *
                                  RESIDENT Table
                                  ORDER BY Month; 
                                  
                                  DROP TABLE Table; 
                                  
                                  AcumTable:
                                  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

                                      AmountMonthMonthAcum
                                      2141214
                                      8662652
                                      8193167
                                      6554488
                                      3955-93
                                      7516844
                                      926782
                                      2618179
                                      3469167
                                      57610409
                                      64611237
                                      66812431


                                      And what i need is

                                       

                                      AmountMonthMonthAcum
                                      2141214
                                      10802866
                                      18993819
                                      25544655
                                      29495395
                                      37006751
                                      46267926
                                      48878261
                                      52339346
                                      580910576
                                      645511646
                                      712312668


                                      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:

                                          http://community.qlik.com/forums/t/43113.aspx

                                          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.