Jim,
Appreciate your taking the time to write. Unfortunately I do need to
have the period (month/year) as part of the table. What I plan to do,
is to create two pivots with 'Delinquent' as the page field. In first
pivot the page field is set to "YES" and in the second pivot page
field shows both YES+NO. I will then write some VBA code to create a
'replica' of the pivot and compute the percentages.
Thanks again for your help - your earlier suggestion on including
optional page fields is working great!
kannan
On Apr 11, 4:38 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com> wrote:
> Depending on whether you are willing to change your format this can be done
> using a field setting option.
>
> Column field -> Account type
> Row field -> Delinquent
> Data field -> Amount
>
> Right click on the Data field and select
> Field Settings -> Options -> Show Data As ->% of Column...
>
> Sum of Amount Deliquent
> AccountType YES NO Grand Total
> Type1 1.72% 52.97% 41.91%
> Type2 98.28% 1.27% 22.20%
> Type3 0.00% 12.87% 10.10%
> Type4 0.00% 32.89% 25.80%
> Grand Total 100.00% 100.00% 100.00%
>
> --
> HTH...
>
> Jim Thomlinson
>
>
>
> "kansaskan...@gmail.com" wrote:
> > I have the following data in a pivot table.
>
> > a) Account Type (4 types of accounts)
> > b) Loan amount
> > c) Delinquent (payment past due date = yes/no)
> > d) Month, year
>
> > The data looks like this.
> > ===================================================
> > LoanDate Delinquent Account
> > LoanAmount
> > ===================================================
> > Feb-05 NO Type1 409
> > Aug-05 NO Type1 1260
> > Nov-05 YES Type2 2550
> > Jul-05 NO Type3 1936
> > May-05 NO Type4 2943
> > Feb-05 NO Type1 198
> > Apr-05 NO Type1 2309
> > Apr-05 NO Type1 1815
> > Jan-05 NO Type4 2004
> > Jan-05 NO Type1 1975
> > Jan-05 YES Type2 1516
> > Jan-05 YES Type1 71
> > Feb-05 NO Type2 191
> > ===================================================
>
> > I have the month/year as a row field, Account Type as a column field,
> > and Delinquent as a page field. The data field is either dollar
> > amounts or count of loans.
>
> > In the pivot table, I would like to include a field that shows
> > Delinquent loans as a percent of all (delinquent and non-delinquent)
> > loans for each Account Type. How would I do this? Any suggestions
> > would be welcome.
>
> > Thank you very much.
>
> > p.s. I went through Debra Dalgleish's book Excel Pivot Table Recipe
> > Book, and the section on calculated fields, but could not figure it
> > out.- Hide quoted text -
>
> - Show quoted text -
|
Similar Threads
