Running Total

[#1]

How do calculate a running total (such as a bank balance) in a table box?

I would like to calculate the running total for the following bank file

No     Detail               Amount

1         Start balance     10

2          Deposited           5

3          Withdrawn         -2

to get the following Table Box:

No     Detail               Amount     Balance

1         Start balance     10          10

2          Deposited           5          15   

3          Withdrawn         -2          13

If have tried to use the following

SELECT

    `BankNo` As 'No',

     `BankDetail` As 'Detail',

    `BankAmount` As 'Amount',

    `Amount` + Previous(`Amount`) As 'Balance'

FROM `db`.`bank` AS `bank`

but I get the error message Function Previous does not exist?

[giakoum]

SELECT

    `BankNo` As 'No',

     `BankDetail` As 'Detail',

    `BankAmount` As 'Amount',

    if(isnull(previous(`BankAmount`)), `BankAmount`, `BankAmount`+ Peek(`Balance`) As 'Balance'

FROM `db`.`bank` AS `bank`

[Anonymous]

Instead of Previous, use Peek and peek at the previous Balance + add the current amount.

[#4]

Thanks for the prompt reply. I now get the following error on running Reload:

SqlState: 37000, ErrorCode: 1305, ErrorMsg: [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.28]FUNCTION mansys17.previous does not exist. Any further suggestion?

[giakoum]

sorry my mistake...

Note that [No] is better to use than 'No' for field names.

LOAD

'No',

'Detail',

'Amount',

if(isnull(previous(`Amount`)), `Amount`, `Amount`+ Peek(`Balance`) As 'Balance';

SQL SELECT

    `BankNo` As 'No',
    `BankDetail` As 'Detail',
    `BankAmount` As 'Amount'
FROM `db`.`bank` AS `bank`

[#6]

If use either peek or above instead of previous I still get the error:

SqlState: 37000, ErrorCode: 1305, ErrorMsg: [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.28]FUNCTION mansys17.above does not exist

for for instance :

if(isnull(above(`BankAmount`)), `BankAmount`, `BankAmount`+ Peek(`Balance`)) As 'Balance'  or

if(isnull(peek(`BankAmount`)), `BankAmount`, `BankAmount`+ Peek(`Balance`)) As 'Balance'

[giakoum]

this is not correct : if(isnull(peek(`BankAmount`)), `BankAmount`, `BankAmount`+ Peek(`Balance`)) As 'Balance'

use this as in my above message :

LOAD

'No',

'Detail',

'Amount',

if(isnull(previous(`Amount`)), `Amount`, `Amount`+ Peek(`Balance`) As 'Balance';

SQL SELECT

    `BankNo` As 'No',
    `BankDetail` As 'Detail',
    `BankAmount` As 'Amount'
FROM `db`.`bank` AS `bank`

[#8]

Hai,

Please find the attachment , It may helps you.

[#9]

Again thanks for the reply. I am new at Qlikview and still finding my feet.

I tries the following: (The full script):

LOAD

'No' As `No`,

'Date' As 'Date',

'Type' As 'Type',

'Detail' As 'Detail',

'Amount' As 'Amount',

if(isnull(previous(`Amount`)), `Amount`, `Amount`+ Peek(`Balance`)) As 'Balance';

SQL SELECT

    `BankNo` As `No`,

    `BankDate` As 'Date',

    `BankType` As 'Type',

    `BankDetail` As 'Detail',

    `BankAmount` As 'Amount'

FROM `mansys17`.`bank` AS `bank`

I now get the repot but without the correct data e.g.

No     Date     Type     Detail  Amount     Balance

No     Date     Type     Detail  Amount     -81 000.00

No     Date     Type     Detail  Amount     -81 100.00

e.g. instead of the data the captions are displayed instead of the data, except for the Balance column. I am also not sure where the balance amount comes from. It is not the correct figure

[#10]

Thanks for the example. I am still testing Qlickview and are using the Personal edition to do it, and can therefor not open the example. Is it possible to send the example as a reply