Solved: Re: Running Total - Qlik Community

Anonymous · ‎2012-11-02

I have a pivot table in which I have debits and credits. At the beginning of the year, there is a beginning balance of $600,000. I want to be able to see in the pivot by date how this total changes based on the debits and credits. In my expression for Running Total, I have the following: If (FACTTYPE = 'CHARGEOFF',600000- Sum(CHARGEOFFAMOUNT)). I also need to add when FACTTYPE = RECOVERY as well.

All suggestions welcomed.

Thanks!

Temeika

Anonymous · ‎2012-11-05

Thanks everyone for your help. I finally got my running total expression to work. Here is my expression:

Balance-RangeSum(Above(TOTAL Column(1) - Column(2), 0, NoOfRows(TOTAL)))

I created a field in my script to hold the starting Balance of $600,000.

View solution in original post

MayilVahanan · ‎2012-11-02

HI

Try like this

If (wildMatch(FACTTYPE, 'CHARGEOFF','RECOVERY'),600000- Sum(CHARGEOFFAMOUNT))

It inclucde both RECOVERY and CHARGEOFF

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

Anonymous · ‎2012-11-02

Thanks for your quick response. Using what you suggested reset the running total to $600,000 when it increments to a RECOVERY. See RUNNINGTOTAL2 in the image

MayilVahanan · ‎2012-11-02

HI

I think there is no CHARGEOFFAMOUNT for recovery then..did u check it?? whether there is CHARGEOFFAMOUNT for recovery or not?

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

MayilVahanan · ‎2012-11-02

HI

I think try like this:

If (FACTTYPE = 'CHARGEOFF',600000- Sum(CHARGEOFFAMOUNT),if(FACTTYPE = 'RECOVERY', 600000- Sum(creditAmount)))

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

Anonymous · ‎2012-11-02

I am trying your next suggestion, but I am not getting the expect results. I will post the output of my current excel spreadsheet that gives the expected results shortly. I appreciate your help.

Anonymous · ‎2012-11-02

These are the results I would like to see.

Date	DEBIT	CREDIT	BALANCE

01/01/2012			$600,000.00
01/04/2012	$ 9,943.65		$590,056.35
01/06/2012	$159,782.99		$430,273.36
01/19/2012	$ 4,342.81		$425,930.55
01/25/2012	$ 1,800.00		$424,130.55
02/01/2012	$ 10,500.00		$413,630.55
02/09/2012	$ 382.51		$413,248.04
02/14/2012	$ 1,364.91		$411,883.13
02/16/2012	$ 1,000.00		$410,883.13
02/17/2012		$159,779.04	$570,662.17
02/23/2012	$ 406.00		$570,256.17
02/29/2012	$ 198.10		$570,058.07
03/02/2012		$ 5,130.17	$575,188.24

Gysbert_Wassenaar · ‎2012-11-02

try: 600000 - rangesum(above(sum(CHARGEOFFAMOUNT),0,rowno()) + rangesum(above(sum(creditAmount),0,rowno())

talk is cheap, supply exceeds demand

MayilVahanan · ‎2012-11-02

hi

PFA

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

swuehl · ‎2012-11-02

Maybe try something like

= 600000 - rangesum(above(sum(CHARGEOFFAMOUNT),0,rowno(total) )) + rangesum(above(sum(RECOVERYAMOUNT),0,rowno(total) ))

assuming that RECOVERYAMOUNT is the field that returns your credit amounts. If you have only one field that you need to filter by FACTTYPE, try

= 600000 - rangesum(above(sum({<FACTTYPE = {CHARGEOFF}>} CHARGEOFFAMOUNT),0,rowno(total) )) + rangesum(above(sum({<FACTTYPE = {RECOVERY}>} CHARGEOFFAMOUNT),0,rowno(total) ))