Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table: Totals for "Last 12 months" and "Preceding 12 months"
I have a Pivot Table which shows revenues for each month in a rolling
24-month period. (That is, the 24 months are not two calendar years, but start any month and end 24 months later). I need to add: subtotals for the last 12 months, and the 12-months prior to the last 12 months. Apr -2001 $2,471,802 May-2001 $2,637,046 ... ... ... Feb-2002 $2,323,360 Mar-2002 $1,328,335 Last 12Month ???????? Prec 12Month ???????? How do I do this with VBA in the Pivot Table itself? Or will I have to do this in Excel, after the Pivot has been calculated? Thank you! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table: Totals for "Last 12 months" and "Preceding 12 m
Thank you, Jim, for your response and advice. Do I understand you
correctly to mean: Step 1. In Source Data create two columns for Current 12 month and Prior 12 month totals. Step 2. Then any month (=row) that I pick will have a corresponding 12 month and 24 month total. ?? Jim Thomlinson wrote: You need to add a flag to your Source Data that will indicate Current 12 or Prior 12 based on the year and month. That will allow you to divide your periods for aggregation. Then add that to your pivot table... Let me know if that does not make sense. -- HTH... Jim Thomlinson " wrote: I have a Pivot Table which shows revenues for each month in a rolling 24-month period. (That is, the 24 months are not two calendar years, but start any month and end 24 months later). I need to add: subtotals for the last 12 months, and the 12-months prior to the last 12 months. Apr -2001 $2,471,802 May-2001 $2,637,046 ... ... ... Feb-2002 $2,323,360 Mar-2002 $1,328,335 Last 12Month ???????? Prec 12Month ???????? How do I do this with VBA in the Pivot Table itself? Or will I have to do this in Excel, after the Pivot has been calculated? Thank you! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table: Totals for "Last 12 months" and "Preceding 12 m
Not really... Just add a single extra column. Each cell will contain either
the words "Current" or "Prior" based on the date in that row. So it will look something like this... Date Amount flag 1-Nov-05 100 Prior 1-Dec-05 200 Prior 1-Jan-06 300 Prior 1-Feb-06 400 Current 1-Mar-06 500 Current Now you have a field that you can add to your pivot and aggregate by... flag Date Total Current 1-Feb-06 400 1-Mar-06 500 Current Total 900 Prior 1-Nov-05 100 1-Dec-05 200 1-Jan-06 300 Prior Total 600 Grand Total 1500 -- HTH... Jim Thomlinson " wrote: Thank you, Jim, for your response and advice. Do I understand you correctly to mean: Step 1. In Source Data create two columns for Current 12 month and Prior 12 month totals. Step 2. Then any month (=row) that I pick will have a corresponding 12 month and 24 month total. ?? Jim Thomlinson wrote: You need to add a flag to your Source Data that will indicate Current 12 or Prior 12 based on the year and month. That will allow you to divide your periods for aggregation. Then add that to your pivot table... Let me know if that does not make sense. -- HTH... Jim Thomlinson " wrote: I have a Pivot Table which shows revenues for each month in a rolling 24-month period. (That is, the 24 months are not two calendar years, but start any month and end 24 months later). I need to add: subtotals for the last 12 months, and the 12-months prior to the last 12 months. Apr -2001 $2,471,802 May-2001 $2,637,046 ... ... ... Feb-2002 $2,323,360 Mar-2002 $1,328,335 Last 12Month ???????? Prec 12Month ???????? How do I do this with VBA in the Pivot Table itself? Or will I have to do this in Excel, after the Pivot has been calculated? Thank you! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table: Totals for "Last 12 months" and "Preceding 12 m
Jim,
Appreciate your follow-up, and your suggestion turned out to be just what was needed. A big 'thank you'. kannan Jim Thomlinson wrote: Not really... Just add a single extra column. Each cell will contain either the words "Current" or "Prior" based on the date in that row. So it will look something like this... Date Amount flag 1-Nov-05 100 Prior 1-Dec-05 200 Prior 1-Jan-06 300 Prior 1-Feb-06 400 Current 1-Mar-06 500 Current Now you have a field that you can add to your pivot and aggregate by... flag Date Total Current 1-Feb-06 400 1-Mar-06 500 Current Total 900 Prior 1-Nov-05 100 1-Dec-05 200 1-Jan-06 300 Prior Total 600 Grand Total 1500 -- HTH... Jim Thomlinson " wrote: Thank you, Jim, for your response and advice. Do I understand you correctly to mean: Step 1. In Source Data create two columns for Current 12 month and Prior 12 month totals. Step 2. Then any month (=row) that I pick will have a corresponding 12 month and 24 month total. ?? Jim Thomlinson wrote: You need to add a flag to your Source Data that will indicate Current 12 or Prior 12 based on the year and month. That will allow you to divide your periods for aggregation. Then add that to your pivot table... Let me know if that does not make sense. -- HTH... Jim Thomlinson " wrote: I have a Pivot Table which shows revenues for each month in a rolling 24-month period. (That is, the 24 months are not two calendar years, but start any month and end 24 months later). I need to add: subtotals for the last 12 months, and the 12-months prior to the last 12 months. Apr -2001 $2,471,802 May-2001 $2,637,046 ... ... ... Feb-2002 $2,323,360 Mar-2002 $1,328,335 Last 12Month ???????? Prec 12Month ???????? How do I do this with VBA in the Pivot Table itself? Or will I have to do this in Excel, after the Pivot has been calculated? Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Pivot Table Data with Column "Sum" rather than "count" defa | Excel Discussion (Misc queries) | |||
Sorting a text strings but omitting preceding "A" or "The" | Excel Worksheet Functions | |||
Pivot Table Report Filter - "OR" instead of "AND" Multiple Filters | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Pivot table "Group and Show Details" vs. "SubTotals" | Excel Programming |