topic Re: Running Total in QlikView

Running Total

Anonymous — Fri, 02 Nov 2012 18:18:37 GMT

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

Re: Running Total

MayilVahanan — Fri, 02 Nov 2012 18:25:54 GMT

Try like this

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

It inclucde both RECOVERY and CHARGEOFF

Hope it helps

Re: Running Total

Anonymous — Fri, 02 Nov 2012 18:36:00 GMT

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

Re: Running Total

MayilVahanan — Fri, 02 Nov 2012 18:39:38 GMT

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

Re: Running Total

MayilVahanan — Fri, 02 Nov 2012 18:43:18 GMT

I think try like this:

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

Re: Running Total

Anonymous — Fri, 02 Nov 2012 18:59:32 GMT

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.

Re: Running Total

Anonymous — Fri, 02 Nov 2012 19:11:14 GMT

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

Re: Running Total

Gysbert_Wassenaar — Fri, 02 Nov 2012 19:22:40 GMT

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

Re: Running Total

MayilVahanan — Fri, 02 Nov 2012 19:27:04 GMT

PFA

Hope it helps

Re: Running Total

swuehl — Fri, 02 Nov 2012 19:30:35 GMT

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) ))

Re: Running Total

Anonymous — Fri, 02 Nov 2012 19:46:04 GMT

I appreciate all of the suggestions. I am trying all of them. I will post the results shortly.

Re: Running Total

Anonymous — Mon, 05 Nov 2012 19:58:06 GMT

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.

Re: Running Total

Wed, 19 Feb 2014 07:16:00 GMT

Hi!!,

I need to add one dimesion like Credit Area and need to evaluate balance Credit Area wise in the table.

With ur expression its work perfect for the single Credit Area. My requirement is User can select multiple Credit Areas for the Account and Balance can be calculated.

I am able to get the opening balance Credit Area wise but to calculate date wise balance Its not working.

Like in your case Fact type.

Kindly suggest.

Regards,

Nitin