How to get two different grouped pivot tables from the same datatable?

[daniel.bash]

Hi,

I have one data sheet that looks like this:

Date Value (€)
2008-02-01 450
..
..
..
2009-06-03

From the data sheet (Data) I have created a pivot table grouped by
months and years (Pivot 1) with the sum of value. That is no problem.
But later when I create one more sheet where I want the data to be
grouped by weeks (Pivot 2) the grouping changes in Pivot 1 to weeks
too.

Does anyone know how to get around this? How can you get for example
get one pivot graph showing the sum per week and one per month in the
same Excel file using grouping on dates?

Help with this much appreciated.

/Daniel
Sweden

 

[Bob Umlas]

Wow -- looks buggy to me!


Hi,

I have one data sheet that looks like this:

Date Value (€)
2008-02-01 450
..
..
..
2009-06-03

From the data sheet (Data) I have created a pivot table grouped by
months and years (Pivot 1) with the sum of value. That is no problem.
But later when I create one more sheet where I want the data to be
grouped by weeks (Pivot 2) the grouping changes in Pivot 1 to weeks
too.

Does anyone know how to get around this? How can you get for example
get one pivot graph showing the sum per week and one per month in the
same Excel file using grouping on dates?

Help with this much appreciated.

/Daniel
Sweden

 

[David Heaton]

Hi,

I have one data sheet that looks like this:

Date                             Value (€)
2008-02-01                    450
.
.
.
2009-06-03

From the data sheet (Data) I have created a pivot table grouped by
months and years (Pivot 1) with the sum of value. That is no problem.
But later when I create one more sheet where I want the data to be
grouped by weeks (Pivot 2) the grouping changes in Pivot 1 to weeks
too.

Does anyone know how to get around this? How can you get for example
get one pivot graph showing the sum per week and one per month in the
same Excel file using grouping on dates?

Help with this much appreciated.

/Daniel
Sweden

Daniel,

I'm not sure if this is the answer to your question but...

In Excel 2003 when you create a second pivot from the data sheet you
are asked if you want base the new Pivot Table on the original to save
memory.
If you do this then the two pivot tables will be linked.

If you answer NO to that question then a new pivot table will be
created independent of the first.

if that doesnt work you could create another sheet that mirrors your
original and base your new pivot table on that...its ugly but it would
work.


HTH

Regards

David

 

[daniel.bash]

Daniel,

I'm not sure if this is the answer to your question but...

In Excel 2003 when you create a second pivot from the data sheet you
are asked if you want base the new Pivot Table on the original to save
memory.
If you do this then the two pivot tables will be linked.

If you answer NO to that question then a new pivot table will be
created independent of the first.

if that doesnt work you could create another sheet that mirrors your
original and base your new pivot table on that...its ugly but it would
work.

HTH

Regards

David

Dear David,

Thanks for your reply.

Yes, indeed it seems like the pivot tables are linked. I am using
Excel 2007, and it does not prompt me if I want to base the new pivot
on the original. It just creates a new pivot table in a new sheet.

I will try the 2nd option and report back.

/Daniel

 

[Shane Devenshire]

Hi,

this is not a bug. In 2003 you are asked if you want to use the same cache
when you build a second pivot table from the first one's data range. If you
want to group two pivot tables differently, as you say, then just choose a
different cache.

However, the solution is the similar in 2007, but with a little twist -
Instead of choosing Insert, PivotTable, with the data range selected press
Alt+D+P and you will be in the 2003 pivot table wizard, proceed throught the
steps (you won't be asked about what Cache to use), when you finish, the
pivot table will be using a different cache and you will be able to group it
differently without affecting the other PT.

 

[daniel.bash]

Hi,

this is not a bug.  In 2003 you are asked if you want to use the same cache
when you build a second pivot table from the first one's data range.  If you
want to group two pivot tables differently, as you say, then just choose a
different cache.

However, the solution is the similar in 2007, but with a little twist -
Instead of choosing Insert, PivotTable, with the data range selected press
Alt+D+P and you will be in the 2003 pivot table wizard, proceed throught the
steps (you won't be asked about what Cache to use), when you finish, the
pivot table will be using a different cache and you will be able to groupit
differently without affecting the other PT.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire










- Show quoted text -

Dear Shane,

Thank you very much! This works great!

Is it possible to access the 2003 pivot guide from the menus in 2007?
The reason why I am asking is because at work I have English Excel and
at home I have Swedish. Unfortunately not all the keyboard shortcuts
are the same.

Have a nice day!

/Daniel

 

[Shane Devenshire]

Hi,

I presume you mean the ribbon? The answer is no, but you can add a button
to the QAT to launch the 2003 pivot table wizard. Press Alt+1, choose
Customize, pick Commands not in the ribbon from the top left dropdown, find
the button called PivotTable and PivotChart Wizard and add it.