topic Re: Running Balance in QlikView

Running Balance

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a question about counting running balance.

I have data like this:

TRX_TYPE	TRX_TYPE_NO	LEDGER_ID	LEDGER_NAME	GL_DATE	GL_YEAR	DESCRIPTION	ACCOUNT_NO	ACCOUNT_NAME	AMOUNT_DR	AMOUNT_CR	OPENING_BALANCE
BALANCE	1	2222	LTD A		2016	BALANCE	2555	HUG			50
PLB	3	2222	LTD A	4/23/2016	2016	A	2555	HUG	1	0
PLB	3	2222	LTD A	4/23/2016	2016	B	2555	HUG	2	0
PLB	3	2222	LTD A	4/24/2016	2016	C	2555	HUG	3	0
PEMB	2	2222	LTD A	4/28/2016	2016	D	2555	HUG	0	4
PEMB	2	2222	LTD A	4/28/2016	2016	E	2555	HUG	0	5
PEMB	2	2222	LTD A	4/25/2016	2016	F	2555	HUG	0	6

The sample data attached.

I want to count the balance for each row.

Example for the second row the result must be (OPENING_BALANCE+AMOUNT_DR-AMOUNT_CR) --> 50+1-0 =51

The third row must be --> 51+2-0 = 53

And so on. Here is the result i expected on my straight table.

GL_DATE	DESCRIPTION	ACCOUNT_NAME	AMOUNT_DB	AMOUNT_CR	BALANCE
-	BALANCE	HUG	0	0	50
4/23/2016	A	HUG	1	0	51
4/23/2016	B	HUG	2	0	53
4/24/2016	C	HUG	3	0	56
4/28/2016	D	HUG	0	4	52
4/28/2016	E	HUG	0	5	47
4/25/2016	F	HUG	0	6	41

FYI i've try using this expression on my BALANCE column:

sum({<GL_YEAR={'$(vYear)'}>}TOTAL SALDO) + RangeSum(Above(TOTAL sum(AMOUNT_DR-AMOUNT_CR), 0, NoOfRows(TOTAL)))

It works fine, but when i load all data (1000000 records) it runs very slow and can't load the table (keep loading)

Anybody can help me with this problem ?

Thanks in advance.

Really appreciate your help

Re: Running Balance

sarvesh — Wed, 22 Feb 2017 08:56:32 GMT

Hi..Chou,

For this type of requirement you can use Pivot Table.

Please remember to mark this as "helpful" & "correct answer" if your query has been solved.

This will help users identify the answers should they come across this thread in the future.

Regard's

Sarvesh Srivastava

Re: Running Balance

Anonymous — Wed, 22 Feb 2017 09:02:54 GMT

Hi Sarvesh,

Can you give me the example please ?

Thanks in advance.

Re: Running Balance

sarvesh — Wed, 22 Feb 2017 09:11:31 GMT

Please watch this video then you can understand how can make total of every column, click on below mentioned video.

QlikView | Pivot Tables - YouTube

Please remember to mark this as "helpful" & "correct answer" if your query has been solved.

This will help users identify the answers should they come across this thread in the future.

Regard's

Sarvesh Srivastava

Re: Running Balance

michael123 — Wed, 22 Feb 2017 10:36:32 GMT

Alternatively you could calculate the balance in the load of the data with peek or previous function. Risk is that the load section takes too long instead though.

Here is an example but there are more here if you google,

Running Sum on Load Script

Regards,

/Michael

edit: Link wrong