4 Replies Latest reply: Nov 24, 2011 12:31 PM by Marcos Herrera RSS

    Acuumlated Per Month and Year

      Hi for all

       

      I load a table with this fileds

       

      LLave_Movimiento_Acum --> Is a Key formed per AccountID_Year_Month

      Accountnum --> AccountID

      Año_Acum --> Year

      Mes_Acum --> Month

      Importe --Value

       

       

       

      I need create a filed with the Accumlated from months per year in load script

       

      An examplo for the final table

      -----

      LLave_Movimiento_AcumAccountnumAño_AcumMes_AcumImporteAcumm
      1020-201101102020111100100
      1020-201102102020112100200
      1020-201103102020113100300
      1020-201104102020114100400
      1020-201105102020115100500
      1020-201106102020116100600
      1020-201107102020117100700
      1020-201108102020118100800
      1020-201109102020119100900
      1020-20111010202011101001000
      1020-20111110202011111001100
      1020-20111210202011121001200
      1050-201101105020111200200
      1050-201102105020112200400
      1050-201103105020113200600
      1050-201104105020114200800
      1050-2011051050201152001000
      1050-2011061050201162001200
      1050-2011071050201172001400
      1050-2011081050201182001600
      1050-2011091050201192001800
      1050-20111010502011102002000
      1050-20111110502011112002200
      1050-20111210502011122002400

      -------------------------------------------

        • Acuumlated Per Month and Year
          Stefan Wühl

          Hi,

           

          you could use peek() function to retrieve a field value from previous record, this allows you to accumulate, like

           

          INPUT:

          LOAD LLave_Movimiento_Acum,

               Accountnum,

               Año_Acum,

               Mes_Acum,

               Importe,

               Acumm as Acumm_Ref

          FROM

          [http://community.qlik.com/thread/39560]

          (html, codepage is 1252, embedded labels, table is @1);

           

          RESULT:

          LOAD *,

          if(peek(Accountnum)=Accountnum and peek([Año_Acum])=[Año_Acum],peek(Acum_Importe)+Importe,Importe) as Acum_Importe

          resident INPUT order by Accountnum, [Año_Acum] asc, [Mes_Acum] asc;

           

          drop table INPUT;

           

          As I understood your request, I start the accumulation for each account and per year. If you don't want to reset the accumulation on a year change, just remove the "and peek([Año_Acum])=[Año_Acum]" from above if statement.

           

          Hope this helps,

          Stefan