Pivot and YTD
Posted by Brian P on May 30, 2001 10:18 AM
My first pivot table has a category for rows, months in columns going across and total dollars in the data field. How can I create a second pivot with each months YTD total based on the first pivot?
The final goal is to show a chart and dollars with each months total and a second chart and dollars with YTD totals. I don't want to show just one month at a time - I want to show all months for both monthly and YTD totals.
Thanks,
Brian
Posted by Mark W. on May 30, 2001 10:49 AM
Brian, create a copy of your 1st PivotTable,
right-click a cell in the PivotTable's DATA area,
choose the Field... command from the popup menu,
press the PivotTable Field [ Options >> ] button,
set the "Show data as" field to "Running Total in",
set the "Base field" to 'Month' (or whatever the
COLUMN area's field name is), and press [ OK ].
Posted by Brian P on May 30, 2001 11:31 AM
Thanks but still need help
Mark,
Thanks for your help-I like it but...
My problem is that the data that I'm reading from has each month as a column - so each column field/data field in the pivot table is separate. If I had one column field in my data file that was called Month - your solution would work perfect. Any other ideas? or how can I convert my data file so that instead of having 12 columns of values for each record - I have 12 records, one for each month.
Thanks
Brian
, create a copy of your 1st PivotTable,
Posted by Mark W. on May 30, 2001 12:26 PM
Re: Thanks but still need help
Brian, what you have (in relational theory vernacular)
is a repeating group (Month). PivotTables work
best when your data is in 1st Normal Form (1NF)
which doesn't have any repeating groups. As
you suggested you need to change the organization
of your data.
Can you provide about 25 rows of your data set
(including headers)? I'll try to describe how
you can transform your data to 1NF. , create a copy of your 1st PivotTable,
Posted by Brian P on May 30, 2001 1:18 PM
Here's a data example
Thanks Mark
Here's the data example. I think I can do the vba if you can describe what to do. I'm not sure whether to use arrays or formulas or copy paste. some options could be real slow since I will be working with large amounts of data. I could transfer to Access - if you think that would be beneficial.
Cost Center Plan/Actual Cost elements Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
15223 Actual 500100 Reg/Ordinary Time "44,000 " "50,000 " 0 0 0 0 0 0 0 0 0 0
15223 Actual 500200 Overtime 500 "2,000 " 0 0 0 0 0 0 0 0 0 0
15223 Actual 500850 Other Salary/Labor C 10 200 0 0 0 0 0 0 0 0 0 0
15223 Actual Salary Expense "44,510 " "52,200 " 0 0 0 0 0 0 0 0 0 0
15223 Actual 503100 Airfare "1,844 " 0 0 0 0 0 0 0 0 0 0 0
15223 Actual 503110 Lodging 331 0 0 0 0 0 0 0 0 0 0 0
15223 Actual 503120 Meals/Entertain 77 0 0 0 0 0 0 0 0 0 0 0
15223 Actual 503125 Vehicle Rent/Exp 0 140 0 0 0 0 0 0 0 0 0 0
15223 Actual 503135 Auto/Park/Mileage 45 28 0 0 0 0 0 0 0 0 0 0
15223 Actual 503140 Cell Phone 61 (1) 0 0 0 0 0 0 0 0 0 0
15223 Actual 503170 Dues & Licenses 80 0 0 0 0 0 0 0 0 0 0 0
15223 Actual 503400 Other Emp Rel Exp 4 (11) 0 0 0 0 0 0 0 0 0 0
15223 Actual Employee Expenses "2,442 " 156 0 0 0 0 0 0 0 0 0 0
15223 Actual Total "50,000 " "53,000 " 0 0 0 0 0 0 0 0 0
10111 Plan 500100 Reg/Ordinary Time "55,000 " "55,000 " "55,000 " "55,000 " "55,000 " "55,000 " "55,000 " "55,000 " "55,000 " "55,000 " "55,000 " "55,000 "
10111 Plan 500850 Other Salary/Labor C "4,000 " "4,000 " "4,000 " "4,000 " "4,000 " "4,000 " "4,000 " "4,000 " "4,000 " "4,000 " "4,000 " "4,000 "
10111 Plan Salary Expense "59,000 " "59,000 " "59,000 " "59,000 " "59,000 " "59,000 " "59,000 " "59,000 " "59,000 " "59,000 " "59,000 " "59,000 "
10111 Plan 503100 Airfare "1,000 " "1,000 " "1,000 " "1,000 " "1,000 " "1,000 " "1,000 " "1,000 " "1,000 " "1,000 " "1,000 " "1,000 "
10111 Plan 503110 Lodging 500 500 500 500 500 500 500 500 500 500 500 500
10111 Plan 503120 Meals/Entertain 500 500 500 500 500 500 500 500 500 500 500 500
10111 Plan 503125 Vehicle Rent/Exp 20 20 20 20 20 20 20 20 20 20 20 20
10111 Plan 503135 Auto/Park/Mileage 20 20 20 20 20 20 20 20 20 20 20 20
10111 Plan 503140 Cell Phone 2 2 2 2 2 2 2 2 2 2 2 2
10111 Plan 503400 Other Emp Rel Exp (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3) (3)
10111 Plan Employee Expenses "1,446 " "1,446 " "1,446 " "1,446 " "1,446 " "1,446 " "1,446 " "1,446 " "1,446 " "1,446 " "1,446 " "1,446 "
10111 Plan Total "70,000 " "70,000 " "70,000 " "70,000 " "70,000 " "70,000 " "70,000 " "70,000 " "70,000 " "70,000 " "70,000 " "70,000 "
Thanks
, what you have (in relational theory vernacular)
Posted by Mark W. on May 30, 2001 2:05 PM
Re: Here's a data example
Brian, could you provide a CSV sample? Mark
Posted by Brian P on May 30, 2001 2:17 PM
Re: Here's a data example
another try
Cost Center,Plan/Actual,Cost elements,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
15223,Actual,500100 Reg/Ordinary Time,"44,000 ","50,000 ",0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0
15223,Actual,500200 Overtime,500 ,"2,000 ",0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0
15223,Actual,500850 Other Salary/Labor C,10 ,200 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0
15223,Actual,Salary Expense,"44,510 ","52,200 ",0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0
15223,Actual,503100 Airfare,"1,844 ",0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0
15223,Actual,503110 Lodging,331 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0
15223,Actual,503120 Meals/Entertain,77 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0
15223,Actual,503125 Vehicle Rent/Exp,0 ,140 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0
15223,Actual,503135 Auto/Park/Mileage,45 ,28 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0
15223,Actual,503140 Cell Phone,61 ,(1),0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0
15223,Actual,503170 Dues & Licenses,80 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0
15223,Actual,503400 Other Emp Rel Exp,4 ,(11),0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0
15223,Actual,Employee Expenses,"2,442 ",156 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0
15223,Actual,Total,"50,000 ","53,000 ",,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0
10111,Plan,500100 Reg/Ordinary Time,"55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 "
10111,Plan,500850 Other Salary/Labor C,"4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 "
10111,Plan,Salary Expense,"59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 "
10111,Plan,503100 Airfare,"1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 "
10111,Plan,503110 Lodging,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500
10111,Plan,503120 Meals/Entertain,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500
10111,Plan,503125 Vehicle Rent/Exp,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20
10111,Plan,503135 Auto/Park/Mileage,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20
10111,Plan,503140 Cell Phone,2 ,2 ,2 ,2 ,2 ,2 ,2 ,2 ,2 ,2 ,2 ,2
10111,Plan,503400 Other Emp Rel Exp,(3),(3),(3),(3),(3),(3),(3),(3),(3),(3),(3),(3)
10111,Plan,Employee Expenses,"1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 "
10111,Plan,Total,"70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 "
Brian, could you provide a CSV sample? : Thanks Mark
Thanks : , what you have (in relational theory vernacular) : is a repeating group (Month). PivotTables work : best when your data is in 1st Normal Form (1NF) : which doesn't have any repeating groups. As : you suggested you need to change the organization : of your data.
Posted by Mark W. on May 31, 2001 7:19 AM
Re: Here's a data example
I entered your data into cells A1:027...
1. Recognize that you need to allow for c-1
additional rows (in this case c=12).
2. Enter the values 1 and 2 into cells P2 and
P3 respectively.
3. Select cells P1:P2 and double-click the fill
handle in the lower right-hand corner of the
selection.
4. Copy the resultant selection; select cells
P28:P313. Note: 313=r(c-1)+n-1 where r is the
number of data rows (26) and n is the next row
number (28).
5. Select rows 2:313 and Sort Ascending on Column
P. You've now distributed the necessary rows
to transpose the repeating group.
6. Enter the array formula, {=TRANSPOSE($D$1:$O$1)},
into cells Q2:Q13, and then double-click the
fill handle.
7. Enter the array formula, {=TRANSPOSE($D2:$O2)},
into cells R2:R13, and double-click the fill handle.
8. Select columns Q:R, Copy, and Paste Special
Values.
9. Delete columns D:P.
10. Select columns A:C, choose the
Edit | Go To... | Special... menu command, and
click the "Blanks" radio button.
11. Type =A2 and then Control+Enter.
12. Select columns A:C, Copy, and Paste Special
Values.
13. Enter appropriate column headers into cells
D1:E1, and you're done.
another try Cost Center,Plan/Actual,Cost elements,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar
Posted by Brian P on May 31, 2001 9:33 AM
Hey Mark - Thanks Very Much!!!!
I haven't had a chance to do it yet - but I read through your note very carefully and understand what you're doing. I had seen something similar to this but much simpler. This will be sweet and I've learned a lot from you.
Thanks. Love this stuff when it works. Drives me nuts when I don't have a good answer and I can't stop thinking about it until I do. Now I've got to get back to Performance Goals - ugh
Brian P.
I entered your data into cells A1:027... 1. Recognize that you need to allow for c-1
another try : Cost Center,Plan/Actual,Cost elements,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar 15223,Actual,500100 Reg/Ordinary Time,"44,000 ","50,000 ",0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,500200 Overtime,500 ,"2,000 ",0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,500850 Other Salary/Labor C,10 ,200 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,Salary Expense,"44,510 ","52,200 ",0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503100 Airfare,"1,844 ",0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503110 Lodging,331 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503120 Meals/Entertain,77 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503125 Vehicle Rent/Exp,0 ,140 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503135 Auto/Park/Mileage,45 ,28 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503140 Cell Phone,61 ,(1),0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503170 Dues & Licenses,80 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503400 Other Emp Rel Exp,4 ,(11),0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,Employee Expenses,"2,442 ",156 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,Total,"50,000 ","53,000 ",,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 10111,Plan,500100 Reg/Ordinary Time,"55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 " 10111,Plan,500850 Other Salary/Labor C,"4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 " 10111,Plan,Salary Expense,"59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 " 10111,Plan,503100 Airfare,"1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 " 10111,Plan,503110 Lodging,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 10111,Plan,503120 Meals/Entertain,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 10111,Plan,503125 Vehicle Rent/Exp,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 10111,Plan,503135 Auto/Park/Mileage,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 10111,Plan,503140 Cell Phone,2 ,2 ,2 ,2 ,2 ,2 ,2 ,2 ,2 ,2 ,2 ,2 10111,Plan,503400 Other Emp Rel Exp,(3),(3),(3),(3),(3),(3),(3),(3),(3),(3),(3),(3) 10111,Plan,Employee Expenses,"1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 " 10111,Plan,Total,"70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 "
, could you provide a CSV sample?
Posted by Mark W. on May 31, 2001 1:06 PM
Some edits...
Step 4 should read...
4. Copy the resultant selection; select cells
P28:P313. Note: 313=r(c-1)+n-1 where r is the
number of data rows (26) and n is the next row
number (28); and choose Edit | Paste.
another try : Cost Center,Plan/Actual,Cost elements,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar 15223,Actual,500100 Reg/Ordinary Time,"44,000 ","50,000 ",0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,500200 Overtime,500 ,"2,000 ",0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,500850 Other Salary/Labor C,10 ,200 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,Salary Expense,"44,510 ","52,200 ",0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503100 Airfare,"1,844 ",0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503110 Lodging,331 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503120 Meals/Entertain,77 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503125 Vehicle Rent/Exp,0 ,140 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503135 Auto/Park/Mileage,45 ,28 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503140 Cell Phone,61 ,(1),0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503170 Dues & Licenses,80 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,503400 Other Emp Rel Exp,4 ,(11),0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,Employee Expenses,"2,442 ",156 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 15223,Actual,Total,"50,000 ","53,000 ",,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 10111,Plan,500100 Reg/Ordinary Time,"55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 ","55,000 " 10111,Plan,500850 Other Salary/Labor C,"4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 ","4,000 " 10111,Plan,Salary Expense,"59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 ","59,000 " 10111,Plan,503100 Airfare,"1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 ","1,000 " 10111,Plan,503110 Lodging,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 10111,Plan,503120 Meals/Entertain,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 ,500 10111,Plan,503125 Vehicle Rent/Exp,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 10111,Plan,503135 Auto/Park/Mileage,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 ,20 10111,Plan,503140 Cell Phone,2 ,2 ,2 ,2 ,2 ,2 ,2 ,2 ,2 ,2 ,2 ,2 10111,Plan,503400 Other Emp Rel Exp,(3),(3),(3),(3),(3),(3),(3),(3),(3),(3),(3),(3) 10111,Plan,Employee Expenses,"1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 ","1,446 " 10111,Plan,Total,"70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 ","70,000 "
, could you provide a CSV sample?
