Subtotals for pivot table with calcuations

[scot72001]

I believe this is not possible (as i am getting an error saying so) but does anyone have a workaround for getting monthly subtotals on a pivot table where there are calculated items?

i've tried two ways:
- highlighted date in the pivot and clicking field settings and then custom and then sum. i get an error telling me that subtotals not allowed with calculated items.
- creating a second field for the date - month and putting it beside the date in the pivot table. The issue i am having here is that for each month of the year it is putting every date beside it even if the field is blank. So for November i am getting dates from October, September.... but the data is blank - because they are not part of that month. If i can get rid of these blanks this would be a solution. Anyone have any ideas?

Thanks
Michael

 

[Andrew Poulsom]

Welcome to MrExcel.

Can't you Group the dates by Month and Year?

 

[scot72001]

Welcome to MrExcel.

Can't you Group the dates by Month and Year?

Hi

It comes up with an error saying that the pivot table is already grouped (i have not done this) and that you cannot group on a calculated field

Any more ideas?

Thanks
Michael

 

[Andrew Poulsom]

Can you post a small but representative sample of your data and what's in the pivot table?

 

[scot72001]

Can you post a small but representative sample of your data and what's in the pivot table?

In sample below i want to pivot date on rows with test and test 2 both on columns and i want to, lets say have test/test2 as a calc field. i then want to get a monthly subtotal for test and test2.

29/11/2013 30/11/2013 01/12/2013 02/12/2013 03/12/2013 04/12/2013 05/12/2013 06/12/2013 07/12/2013 08/12/2013 09/12/2013 10/12/2013 11/12/2013 12/12/2013 <colgroup><col></colgroup><tbody> </tbody>	November November December December December December December December December December December December December December <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody>	test test test test test test test test test2 test2 test2 test2 test2 test2 test2 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody> <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody>	3 4 5 6 6723 231 423 23 423 423 423 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[Andrew Poulsom]

That would require a calculated item rather than a calculated field. And you can't have a calculated item if the field is grouped, sorry.

 

[scot72001]

That would require a calculated item rather than a calculated field. And you can't have a calculated item if the field is grouped, sorry.

sorry yes that is what i meant a calculated item which is what i'm using. thanks for your help anyway. its surprising its restricted with this.