How to calculate Statement from a list of records?

[#1]

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

f

[Anil_Babu_Samineni]

Fabio,

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

[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.

t1:

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

resident YourFirstLoadOfTheseData order by Bank, Date desc;

t2:

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

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

- Marcus

[maxgro · Accepted solution]

an idea in the attachment, script is

B:

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  

];

C:

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;

FINAL:

LOAD

  [Name Bank],

  Date,

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

  Amount

Resident C

Order By [Name Bank], Date;

DROP Table C;

[#5]

Very Helpful.

Thanks a lot!!

[#6]

Sorry Mate

that's perfect if only a have 1 Amount in each date.

But in a case I have more than  one in the selected date it show me the different CumAmount and not the total Amount. Any idea how to solve it?

[maxgro]

post a small example (.qvw)

[#8]

HI

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

That's the script I'm using

B:

LOAD Conto,

     dt_reg, 

     [Nr Reg],

     [Imp Ope],

     [Descr Top],

     F6    

FROM

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

C:

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;

FINAL:

LOAD

  [Conto],

  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.

Conto	dt_reg	Nr Reg	Imp Ope	Descr Top
DEUTSCHE	03/01/2015	0000018044	1384,73	INCOME
DEUTSCHE	03/01/2015	0000018746	-0,8	EXPENSE
DEUTSCHE	02/04/2015	0000018747	3259,2	INCOME
DEUTSCHE	31/12/2014	0000018738	-12679,08	SDD

[maxgro]

try the attachement, always with test data of my previous post