2007 - Pivot Table With MANY Variables

[ladyck3]

I don't know if this is something that can be done in one or two Pivot Tables. Currently I have individual Pivots for each entity but that's a mess and not very efficient. SO.... HELP

My ultimate goal is to possibly have this all in one Pivot but not being very pivot saavy, well here I am, to learn.

Situation: Orders for Widgets. Some have covers and some do not. Those with covers can be Gold or Silver. Here is what I am trying to get out of my pivot table and I've been working for days on this. Not being a Pivot table guru, I ask, what is the easiest way to obtain the following:

Orders come from 5 Sites
Orders can be made by muliple individuals

I need to present the following this is reported by week:
Note: My data has a week column in it which can be used for filtering.

How many widgets were ordered by Site > How many with covers > the % with covers vs. all ordered

This needs to be broken down by:
Site
# Ordered
# With Covers
% Orders with Covers

Individual Ordering
# Ordered
# With Covers
% Orders with Covers

And a variation:

By Cover Color Gold
# Ordered
# Gold Ordered
% of Gold

By Cover Color Silver
# Ordered
# Silver Ordered
% of Silver (Silver will have a subset, Glossy or Brushed Finish)


Does this sound confusing?
Well I've been racking my brain for over a week, actually 2 and I'm about numb. HELP!

Thanks in advance...
Laurie


ladyck3
aka: Laurie

 

[ladyck3]

I thought it was something that I could do all in one pivot but its not possible, there are just way too many variables.

As to what version of excel, it is stated in the title of this query.... Excel 2007.

Bottom line, forget all of the stories above, I need an education on the percentage options in a pivot table. That is where my FRUSTRATION is centered. If I use % of Total, each line is a percentage of the Grand Total, but I want it to be the pecentage of the weekly breakdown not of the Grand Total.

I've not used the Subtotal collapsing feature in Excel so maybe that is another way to go.

I'm so lost at this point....

Here is what my pivot looks like, but if I expand the week, the numbers are again of the grand total not of the Week score.


Values
Row Labels Tickets NTF NTF %
WK 06 71 67 33%
Wk 07 76 54 26%
Wk 08 68 22 11%
Wk 09 27 19 9%
Wk 10 51 28 14%
Wk 11 34 16 8%
Grand Total 327 206 100%

The NTF % for Week 6 should be 94%... (67/71) but it shows 33% of the total NTF column. If I change the pivot table option to do % of row, they all come up #N/A

I don't know what the heck to do!!!!!

Sorry for all the widget confusion trying to mask this..

And its not a database, its from another worksheet within the workbook and it grows about 100+ per week. It is updated on Mondays with the previous week's data.

Help me with the percent options in the pivot table, understanding them.

That is as confusing to me as Format Cells > Custom and all the options in there.... I have no clue what they all me with the zeros and # and whatever... is there some place on the web to get an explanation of that list of options? Oops, another tangent, sorry.

Just help me to understand the percent options PLEASE!

ladyck3
aka: Laurie

 

[SkipVought]

Maybe just forget PTs and consider using the AVERAGEIFS() function, in which you can specify several criteria and get and average for the conbination of those creteria. You may have better control over your summary table(s) using this approch, or even a combination of approches on your summary sheet.

So start with this data structure. You can either COPY your week column and use Data > Data Tools > Remove Duplicates OR, my preference, use MS Query via Data > Get External Data > From other data sources > MS Query... to get a DISTINCE list of your weeks and ALWAYS have it current to the Source Data by REFRESHING the QueryTable...
[tt]
Week Tickets NTF NTF %
WK 06
Wk 07
Wk 08
Wk 09
Wk 10
Wk 11
[/tt]
Tickets are a COUNTIF(), NTF are a COUNTIFS() and NTF% is AVERABEIFS()

I would also either use Named Ranges OR, better yet, Structured Table References, by CONVERTING your Source Table to a Structured Table, via Insert > Table > Tables. There is a whole wealth of features in Structured Tables.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Actually the NTF% would be
[tt]
D2: =C2/B2
[/tt]
assuming that the summary table structure I previously posted is in A1.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Ooooo new things to digest, play time!
I have a couple more reports to complete for the day and then I'm jumping in....

New things! Oh goodie.... hang on it might be a bumpy ride

Thanks Skip!

ladyck3
aka: Laurie

 

[ladyck3]

Ok, the questions begin.

There are NO duplicate records, each is unique, now they are some duplicates in some columns but there are No duplicate records themselves.

This MS Query you speak of, there is no external source to link to. There is a system that on Monday's I open and pull the data from the previous week, but there is absolutely no way on God's green earth to link the two.... it just wouldn't happen even if it was possible.

So if there are no duplicates... well I'm aware of Countifs and have used them but have no utlized averageifs

How this is determined is there is a number of record per week. There is a column, let's call it NTF that either has data in the cells or does not... those that do have data are the ones I need the percentage data from... that's the criteria used to determine NTF or not.

If I forget Pivot Tables, I just can't imagine.... well let me find out if I can do averages if there is or is not data in the cells... back to Excel.

SCREAM if you want to stop now


ladyck3
aka: Laurie

 

[ladyck3]

Ok, so I have, using Countifs the number of records for each week.
Next I want to know how many of the cells, by week in column AA have a date in them.

Those with Dates in the cells are considered the NTF items.
Is there a criteria I can use in the Countifs formula that does not need to pull exact data but just if there is any data?

<listening for screaming>

Now remember you taught me countifs and I use that a lot!!! Just need a nudge

ladyck3
aka: Laurie

 

[SkipVought]

You may not have duplicate records. HOWEVER, the Week values ARE DUPLICATED in your table, and in order to summarize you need ONE OCCURRENCE of each Week Value. That's all I was saying. In your case, I'd guess that then next week arrives and so you ADD a row for that week. No sweat! Over and out! 10-4 good buddy!

So assuming that your source data column headings are as you previously posted, WITH THE ADDITION OF NTF, then these are the FORMULAS you could use in the THREE columns in your summary table, assuming that your summary table begins in A1 and you are using Named Ranges based on Column Headings...
[tt]
Tickets B2: =COUNTIF(Week,$A2)
NTF C2: =COUNTIFS(Week,$A2,NTF,"<>"&"")
NTF % D2: =C2/B2
[/tt]
and copy/paste down thru rows of data.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Ok, so what I did was this (I still have week 06 tied to each Week 06 record I did not delete duplicates... scared to)

In C2 I put this

=COUNTIFS('2012 Master File'!Z2:Z71,"<>"&"")

And it worked, I got the number I was seeking, now please explain .. I get the countifs part but the criteria what is this telling Excel to do... <> is that any data & would be and then "" is nothing

So count if <> any criteria and add nothing?

LEARNING MODE: ON


ladyck3
aka: Laurie

 

[SkipVought]

COUNT any occurrence in Z2:Z71 that is NOT BLANK

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}