Pivot tables

[sports_brah]

Oh the joy. So much joy in fact that I've decided to go over them once again!

They seem straightforward enough, but I'm having a little problems here and there, and there are some superb people here with fantastic knowledge.

My current issue is with the filters. It seems incredibly clunky to restrict via dates and quarters. Am I doing something wrong, or is it just clunky to use?

There's the data headings (48,000+ entries). I'm trying to slice by the first quarter of 2007.

How I've done it:

Starting:

Add date to row labels, right clicked and added years and quarters

Dragged years and quarters up to the top place and selected filters.

Is there a better practice way of doing this?

Thanks!

 

[Efour]

ok

 

[sports_brah]

ok

?

 

[iamdjdz]

Thought I was coming in here to see some broken small kitchen furniture...

Leaves disappointed.

 

[Seft]

You're pretty much there, but manually grouping dates isn't efficient. You'll save yourself some time if you define year/quarter in your base dataset though. E.g. set a formula using year() in another column and then include that column in the pivot. Alternatively, you could use a calculated field in the pivot.

 

[Efour]

move it to the sub forum might get more looks

 

[sports_brah]

Got a more tricky issue which is bugging me a bit (lot)

I'm trying to:

This is what the model answer looks like...

But when I add the Material code + Material in, it has strange formatting. Not to mention I don't have a clue how to edit the headings to get the results asked for!

Any advice appreciated!

 

[sports_brah]

Thought I was coming in here to see some broken small kitchen furniture...

Leaves disappointed.

Wat.

You're pretty much there, but manually grouping dates isn't efficient. You'll save yourself some time if you define year/quarter in your base dataset though. E.g. set a formula using year() in another column and then include that column in the pivot. Alternatively, you could use a calculated field in the pivot.

Ahhh, I see where you are coming from. We were not exposed to such and idea, but I can see how it'd make things a lot easier for the future, when actually working continuously on the same data set.

Thanks!

P.s Any chance of looking at my latest problem?

move it to the sub forum might get more looks

I can't move my own threads, I just wasn't thinking (which is what happens when you surf GD all day!)

 

[mattyprice4004]

I think my head just exploded.
*goes back to Dogeminer*

 

[londonbairn]

Right click and remove sub totals

 

[gpuerrilla]

I think my head just exploded.
*goes back to Dogeminer*

lol

 

[Theophany]

I think my head just exploded.
*goes back to Dogeminer*

 

[Skeeter]

You need Essbase.

 

[WillyNelson]

First make sure the dates in the data are formatted as dates. If you need to you can highlight the column and use Text to Column to force the format from text.

Stick the Date in the Row Label and make sure there is a value in the table, then right click on any date, select Group and a dates grouping window should come up allowing you to select month/quarter/year.
See this http://chandoo.org/wp/2009/11/17/group-dates-in-pivot-tables/

Much easier than either manually grouping dates or inserting additional columns in the data to extract the month/year.

P.s Any chance of looking at my latest problem?

Right click on the Material Code and untick the subtotal option.
And you don't need to edit any headings, you need to add calculations for Net Sales and Contribution Margin to your data.