• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Running In Total in a Pivot Table

A

Ashley

New Member
PLEASE HELP!! This is making me crazy!


*I need the totals to decrease as they enter new stages...


*I have 14 total loans 'Opened'...When the loan goes into the next stage 'Credit Pulled' I need the 'Opened' count/total to decrease..and when the loan goes from 'Credit Pulled' on to the next stage 'Sub to Proc' the total needs to decrease again..and so on... (eventually the 'Opened' column will be at zero after the loan has gone through all the stages)


Example:

my totals w/in the pivot chart are:


Columns: Opened Credit Pulled Sub to Proc Sent to UW Appraisal Ord

Rows: 1 1 0 0 0

6 4 0 0 0

7 7 3 1 2


Total: 14 12 3 1 2
 
Below are my columns, my rows, and the bottom row are the totals.


Opened, Credit Pulled, Sub to Proc, Sent to UW, Appraisal Ordered

1 1 0 0 0

6 4 0 0 0

7 7 3 1 2


14 12 3 1 2
 
Looking at just a single row:

7 7 3 1 2

I read this as there are 7 loans of concern being accounted for on this line? 7 have been through credit pulled, 3 in "sub to proc", 1 in sent?


If you're wanting that "total" at the begining to decrease by 1 for every step taken, you need to first increase the number. While there are 7 loans, each one has 4 steps, so it needs to start out at 7 * 4 = 28. Perhaps addint another column "Count of steps left". Then, your formula would be something like:

=(# of loans)*4 - SUM(CreditPulled:AppraisalOrdered)


If I've misunderstood, could you show what you would like the original sequence to change to as additional steps are completed?
 
I wish I could send you a print screen :) Anyways, how would I do that formula with in a pivot table? Everytime I try I have issues b/c it won't let me change formulas in the pivot table. Should this be done on my 'raw data' spread sheet first?
 
You would do it in the raw data. Then the PivotTable would sum up the decreasing amounts.
 
okay, I'm sorry I'm so needy...

I just sent you a sample of the data I actually have on the spreadsheet.

I actually have 8 different stages the loan goes through, and around 25 different Branches(locations) and 400 different Loan Officers I'm keeping track of.

So, would I have to do that formula for every person and branch? (b/c each number represents the loan stage per loan officer) I'm really not sure how i could add a column w/your formula b/c i have so many different figures per person...and each person/Loan Officer has at least 5 loans out (some have up to 50)

Clear as mud?? haha ;)
 
Unfortunately, yes, clear as mud. Let's back up a step...what's the real goal you're trying to get to? The PivotTable issue is making things somewhat confusing because it groups things together.


Possible goals:

How many loans are at stage x?

How many loans have not started any stage?

How many stages are left to complete?


Also, I'm assuming(hoping) that your main data table has 1 loan per row, and then multiple columns for each stage...or does it have a "status" like column that just shows the current stage it is at?
 
It unfortunately has one column for all of the different stages titled 'Loan Status' -- I have the pivot table made (maybe incorrectly to show the data I need?)


-In my ROW column I have each Branch and each Loan Officer..so my LO's are sorted by Branch

-In the DATA section I have: Count of Opened, Count of Credit Pulled, C of Submitted to Processing, c of Sent to Underwriting, c of Appraisal Ordered, c of Appraisal Rcvd, c of Clear to Close, and c of Funded (that data is in a date format; so it just counts them and totals them up at the bottom.


-ultimatley is shows everything I need except:

-I have to figure out a way to make the pivot table totals (that are counted)to become an acumulative running total. Meaning, if I have 10 loans in the 'Count of Opened'and 3 of the 10 loans have moved into the next stage 'Count of Credit Pulled' I need the 'count' to subtract the 3 loans from the original total of 10. Therefore, in my 'Count of Opened' it would now say 7, and the 'Count of Credit Pulled' would now say 3.

THANK YOU SO VERY MUCH FOR YOUR HELP!! I TRULEY DO APPRECIATE IT! (If I could attach a screen shot to this I would..it would make this so much easier)
 
I'd like to chime in because I had done some Real Estate analytics. But like Luke M i can't picture what your needs are. I am guessing (without seeing the spreadsheet) that there is some issues with the organization of your data table.


You know, garbage in garbage out. Pivot perform on a certain way and if the data table is organized wrong, that's what pivot table would give you in return.
 
Thanks! I think my raw data is fine (maybe not..idk) but my pivot table gives me every total I need. My boss is just picky and needs an cumlitive total that is based off of the totals that are already provided w/in the pivot table--is it possible to have to seperate rows w/the original totals provided, and then base a cumulative total off of that? ...I know it's confusing. I'm sorry :)
 
If the PivotTable show this now:


Open: 7

Working: 3


Since there's only 1 status column, if the data changes so that there are 4 working now, you should just need to refresh the PivotTable for it to show:


Open: 6

Working: 4


Alternatively, if sounds like a list of all possible statuses and some COUNTIF functions would provide a quick, live dashboard.
 
Hi Ashley ,


If I understand you correctly , you have a data sheet where you just enter the dates on which loans are processed.


So if a loan is opened , a date entry is made in the OPENED column ( say cell B12 , for example ). When the loan passes through the CREDIT PULLED stage , an entry is made in the next column ( say C12 ). This process repeats as the loan passes through each stage. So as a loan passes through its various stages through till the last stage FUNDED , all the previous date entries remain in their respective cells.


So when the pivot table returns a count , it will continue to return the TOTAL OPENED till date , the TOTAL CREDIT PULLED till date , and so on.


I think the solution is to hide the Pivot Table Grand Total row / column , and introduce a new row / column , which will make use of the right formulae , and the counts to display the correct values.


Narayan
 
You must log in or register to reply here.
Back
Top