Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Running Total

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?

15 Replies
giakoum
Honored Contributor II

Re: Running Total

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`

jsn
Honored Contributor

Re: Running Total

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

Not applicable

Re: Running Total

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
Honored Contributor II

Re: Running Total

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`

Not applicable

Re: Running Total

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
Honored Contributor II

Re: Running Total

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`


Not applicable

Re: Running Total

Hai,

Please find the attachment , It may helps you.

Not applicable

Re: Running Total

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

Not applicable

Re: Running Total

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