topic Re: Running Total in QlikView

Running Total

Mon, 22 Oct 2012 08:09:35 GMT

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?

Re: Running Total

giakoum — Mon, 22 Oct 2012 08:31:39 GMT

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`

Re: Running Total

Anonymous — Mon, 22 Oct 2012 08:34:28 GMT

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

Re: Running Total

Mon, 22 Oct 2012 08:35:25 GMT

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?

Re: Running Total

giakoum — Mon, 22 Oct 2012 08:37:45 GMT

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`

Re: Running Total

Mon, 22 Oct 2012 08:41:04 GMT

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'

Re: Running Total

giakoum — Mon, 22 Oct 2012 08:48:37 GMT

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`

Re: Running Total

Mon, 22 Oct 2012 09:15:32 GMT

Hai,

Please find the attachment , It may helps you.

Re: Running Total

Mon, 22 Oct 2012 09:50:14 GMT

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

Re: Running Total

Mon, 22 Oct 2012 09:54:18 GMT

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

Re: Running Total

Tue, 23 Oct 2012 11:49:02 GMT

Thanks a lot. I eventually managed it

Re: Running Total

Tue, 23 Oct 2012 11:49:23 GMT

Thanks a lot. I eventually managed it

Re: Running Total

Tue, 23 Oct 2012 11:50:22 GMT

Thanks a lot. I eventually managed it

Re: Running Total

giakoum — Tue, 23 Oct 2012 13:06:22 GMT

jannielouw, please mark as answered so that we close this discussion.

Thnak you.

Re: Running Total

robert99 — Tue, 23 Oct 2012 16:46:27 GMT

There is an example in the QV manual that I have used

Under peek

load

numsum(B,PEEK('BSUM')) AS BSUM ...

creates an accumulation of B in BSUM

This might also work

Re: Running Total

Wed, 24 Oct 2012 06:11:05 GMT

Thanks. I did get the previous example going