Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculated field in Pivot
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculated field in Pivot
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 " 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculated field in Pivot
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 " 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table, IF function, calculated item versus calculated field | Excel Discussion (Misc queries) | |||
Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table Formulas Calculated Item / Calculated Field | Excel Programming | |||
Unable to change field settings in calculated field in a pivot tab | Excel Discussion (Misc queries) | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) |