8 Replies Latest reply: Jul 13, 2016 10:04 AM by Massimo Grossi RSS

    How to calculate Statement from a list of records?

      Hello Everyone

      I have a doubt of which one is the best option to build a Statement from a list of bank records.


      Let's say I have


      Name Bank                    Date                        Amount         


      Deutsche Bank               31/12/2015                    200          

      Deutsche Bank               07/01/2016                    -100              

      Deutsche Bank               15/03/2016                      50              

      Deutsche Bank                29/03/2016                    -120   


      So my question Should I first calculate the statement  in the set analysis for every day of the calendar, store the data and reload so I can use this new calculate field to have the right statement


      Or I can calculate the statement in the Pivot Table?


      What I want is that for each bank and every day of the calendar the amount show is the sum of the previous records.


      Ex:   Deutsche Bank 01/01/2016 = 200

                31/01/2016 = 100

                20/02/2016  =100

                 31/03/2015 = 30

                   30/06/2016    = 30

      Thanks in advance


        • Re: How to calculate Statement from a list of records?
          Anil Samineni



          Is this possible to share the App


          As i know, it is possible in Oracle Trigger command. If you are the oracle developer please create one trigger for that and then call that trigger to Qlik. Might be helpful


          - ANIL

          • Re: How to calculate Statement from a list of records?
            Marcus Sommer

            You could solve such a case by creating a time-period with From - To per interrecord-functions like Peek() or Previous() ? and afterwards you populate the date with a while-loop.



            load Bank, Date as ToDate, previous(Date) as FormDate, Amount

            resident YourFirstLoadOfTheseData order by Bank, Date desc;



            load Bank, date(FromDate + iterno() - 1) as Date, Amount

            Resident t1 while FromDate + iterno() - 1 <= num(ToDate);


            - Marcus

            • Re: How to calculate Statement from a list of records?
              Massimo Grossi

              an idea in the attachment, script is



              load * inline [

              Name Bank,                    Date,                        Amount        

              Deutsche Bank,               31/12/2015,                    200         

              Deutsche Bank,               07/01/2016,                    -100             

              Deutsche Bank,               15/03/2016,                      50             

              Deutsche Bank,                29/03/2016,                    -120  

              Bank,               05/01/2016,                    2000         

              Bank,               07/01/2016,                    -100             

              Bank,               15/03/2016,                      50             

              Bank,                29/03/2016,                    -120  




              load   date(makedate(2015,12,31) + rowno()-1) as Date AutoGenerate 366;


              join (C) LOAD Distinct [Name Bank]

              Resident B;


              Left Join (C) LOAD * Resident B;

              DROP Table B;




                [Name Bank],


                If([Name Bank]=Peek('Name Bank'), Alt(Amount,0) + Peek('CumAmount'), Alt(Amount,0)) as CumAmount,


              Resident C

              Order By [Name Bank], Date;


              DROP Table C;

              • Re: How to calculate Statement from a list of records?


                I don't know why the attachment button  it doesn't show


                That's the script I'm using





                LOAD Conto,


                     [Nr Reg],

                     [Imp Ope],

                     [Descr Top],



                [C:\Users\fkourennoi\Desktop\Elenco movimenti_2015.xls]

                (biff, embedded labels, table is [Elenco movimenti_2015$]);




                load   date(makedate(2014,12,31) + rowno()-1) as dt_reg AutoGenerate 366;


                join (C) LOAD Distinct [Conto]

                Resident B;


                Left Join (C) LOAD * Resident B;

                DROP Table B;





                  date(date# ([dt_reg],'DD/MM/YYYY'), 'DD-MM-YYYY') AS dt_reg,

                  If([Conto]=Peek('Conto'), Alt([Imp Ope],0) + Peek('Saldo'), Alt([Imp Ope],0)) as Saldo,

                  [Imp Ope],

                  [Descr Top] 

                Resident C

                Order By [Conto], dt_reg;


                DROP Table C;


                And this is the table

                The 3rd of January it doesn't show anything in a pivot table and two option on the Saldo table.


                Contodt_regNr RegImp OpeDescr Top
                DEUTSCHE 03/01/201500000180441384,73INCOME
                DEUTSCHE 03/01/20150000018746-0,8EXPENSE
                DEUTSCHE 02/04/201500000187473259,2INCOME
                DEUTSCHE 31/12/20140000018738-12679,08SDD