Creating multiple totals by year by quarter in pivot table

[#1]

I'm trying to take a series a data into a summary table. (to keep things simple [:)] ) I have a date field and a quantity field and need to produce a summary of this data by quarter by year using pivot tables.

I would like it to look something like the following

2010				2011
{total for all of 2010}				{total for all of 2010}
Jan-Mar 2010	Apr-Jun 2010	Jul-Sep 2010	Oct-Dec 2010	Jan-Mar 2011	Apr-Jun 2011	Jul-Sep 2011	Oct-Dec 2011
{Q1 total}	{Q2 total}	{Q3 total}	{Q4 total}	{Q1 total}	{Q2 total}	{Q3 total}	{Q4 total}

Mine usually ends up close to this but usually falls over on the yearly totals, I've tried putting multiple expressions and using partial sums but to no avail.

Many many thanks in advance for any help anyone can offer me.

Maria

[fernandotoledo]

You can use a calculated dimention to do that, but the best performance would be using Presentation>Show Partial Sums for the Quarter dimension.

Hope it helps

Best regards

Fernando

[#3]

Thanks for the reply Fernando.

I have yearname(datefield) and quartername(datefield) in my expressions and I've ticked the partial sums. This gives me a total for the year but puts it to the right of the quarter totals. This is not what my boss wants and would like the totals under the year.

I know it is trivial but he wants it to look like it did on Excel originally!

Maria

[fernandotoledo · Accepted solution]

See the example atached. Shows both solutions.

Hope it helps!

Thanks ; ) !

Fernando

[#5]

Thanks Fernando, worked like a charm