Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
temiekaclay
Contributor III
Contributor III

Running Total

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. 

untitled.PNG

Thanks!

Temeika

1 Solution

Accepted Solutions
temiekaclay
Contributor III
Contributor III
Author

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.

untitled.PNG

View solution in original post

12 Replies
MayilVahanan

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
temiekaclay
Contributor III
Contributor III
Author

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

RUNNINGTOTAL.PNG

MayilVahanan

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
MayilVahanan

HI

I think try like this:

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

Thanks & Regards,
Mayil Vahanan R
temiekaclay
Contributor III
Contributor III
Author

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.

temiekaclay
Contributor III
Contributor III
Author

These are the results I would like to see.

DateDEBITCREDITBALANCE
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

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


talk is cheap, supply exceeds demand
MayilVahanan

hi

PFA

Hope it helps

Thanks & Regards,
Mayil Vahanan R
swuehl
MVP
MVP

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