Pivot with two total columns? (2K-SR1)

Topic

New Reply

I am trying to design a pivot table with dates as row headings, customer number as column headings, to analyze sales variances. I’m getting a ‘Grand Total’ row and column, but I also want to have a cumulative-to-date grand total as well. (See attached) The number of customers to be included in this table is going to continue to grow to over 100, but not all at one time, so I will need to have the columns generated as part of the pivot table. Any ideas or suggestions?

Reply | Quote

Replies

I think you will need a macro.

The macro only needs to run the code to create the pivot table, then it just has to add the values down the column just past the pivot table.

If you record the steps with the macro recorder, it should get you almost 90% of the way there.
If the pivot is on a separate sheet, you add the code to the sheetactivate event and so whenever the sheet with the pivot is activated, the pivot will be updated and the column generated.

If you need further assistance, please post back, and I can try to offer some details.

Steve

Reply | Quote

I tried to use a macro to create the pivot table but couldn’t quite get it the way I wanted, but it did give me time to re-think my strategy. Instead of using the pivot table to calculate the totals, I calculated the totals and had them included in the data pulled into the pivot table:

Date	Customer	Variance
1/1/05	Cust1	1.00
1/1/05	Cust2	2.00
1/1/05	Cust3	3.00
1/1/05	Week Total	6.00
1/1/05	YTD Total	6.00
1/8/05	Cust1	4.00
1/8/05	Cust2	5.00
1/8/05	Cust3	6.00
1/8/05	Week Total	15.00
1/8/05	YTD Total	21.00

The detail data was pulled onto one worksheet. On a second worksheet the ‘Week Total’ was calculated using a SumIf formula, while the ‘YTD Total’ is a simple C2+B3 formula. A macro copies all the data (pasted as values) onto the sheet that feeds the pivot table. When the pivot table refreshes it treats the two Total values as if they were normal ‘Customer’ values, resulting in a table looking like:

Date	Cust1	Cust2	Cust3	Week Total	YTD Total
1/1/05	1.00	2.00	3.00	6.00	6.00
1/8/05	4.00	5.00	6.00	15.00	21.00

I’m thinking that this method could make it easy to build a pivot table with multiple total columns.

Reply | Quote