Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
HI
Try like this
If (wildMatch(FACTTYPE, 'CHARGEOFF','RECOVERY'),600000- Sum(CHARGEOFFAMOUNT))
It inclucde both RECOVERY and CHARGEOFF
Hope it helps
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
HI
I think there is no CHARGEOFFAMOUNT for recovery then..did u check it?? whether there is CHARGEOFFAMOUNT for recovery or not?
HI
I think try like this:
If (FACTTYPE = 'CHARGEOFF',600000- Sum(CHARGEOFFAMOUNT),if(FACTTYPE = 'RECOVERY', 600000- Sum(creditAmount)))
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.
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 |
try: 600000 - rangesum(above(sum(CHARGEOFFAMOUNT),0,rowno()) + rangesum(above(sum(creditAmount),0,rowno())
hi
PFA
Hope it helps
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) ))