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

 

[SkipVought]

laurie,

I assume that you have ONE source table.

Plese post an example of source data that we can work with.

Once we see how your source data is ofganized, it will be easier to advise regarding the aggregation possibilities in one or more pivot tables.

Skip,

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

 

[ladyck3]

On the attached sample worksheet, Columns titled in yellow are the main criteria. Columns I and J are also part of the equation but not as critical and if I can get a leg up on some of the rest of this, well... I might could just do that part myself <crossing fingers>.

Please see the attached file and your comments and assistance are so very greatly appreciated!!!!

Oh man I'm being firewalled, unable to upload the file.

Its a bit much to paste into this message.

I guess I'm on my own

Thanks anyway

Laurie

ladyck3
aka: Laurie

 

[SkipVought]

Many of us cannot access such files due to company security restrictions.

If you want a broader base of help, please post a suitable example directly in this thread, preferable using TGML TT tags and correstpondingly aligned columns of your sample data.

Skip,

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

 

[ladyck3]

You just politely spoke greek to me
Let me see what I can do in Notepad

ladyck3
aka: Laurie

 

[SkipVought]

a good place to start.

Skip,

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

 

[ladyck3]

I hope this works, its comma separated....

Week#,Mdl,Type ,Color ,Order # , Date ,Site ,OrdrBy,First Order,Prv Problem
Wk 06,ABC,Silver,Glossy ,8223744041,2/2/2012 ,Site4,Agent4,YES ,NO
Wk 06,XYZ,Silver,Brushed,8223744020,2/2/2012 ,Site3,Agent3,YES ,NO
Wk 06,DEF,Silver,Glossy ,8223744107,2/2/2012 ,Site5,Agent5,YES ,NO
Wk 07,GHI,Silver,Brushed,8223876544,2/14/2012,Site4,Agent4,YES ,NO
Wk 07,JKL,Silver,Brushed,8223794738,2/7/2012 ,Site3,Agent3,YES ,NO
Wk 07,ABC,Silver,Brushed,8223882215,2/10/2012,Site5,Agent5,NO ,NO
Wk 07,XYZ,Silver,Glossy ,8223863421,2/13/2012,Site4,Agent4,NO ,NO
Wk 07,DEF,Gold , ,8223709824,1/30/2012,Site3,Agent3,NO ,YES
Wk 07,GHI,Gold , ,8223853120,2/13/2012,Site5,Agent5,NO ,YES
Wk 08,JKL,Silver,Glossy ,8223897172,2/16/2012,Site1,Agent1,NO ,NO
Wk 08,ABC,Silver,Glossy ,8223905431,2/16/2012,Site1,Agent1,NO ,YES
Wk 08,XYZ,Silver,Brushed,8223644159,1/24/2012,Site1,Agent1,NO ,YES
Wk 08,DEF,Silver,Brushed,8223843323,2/10/2012,Site1,Agent1,YES ,NO
Wk 08,GHI,Silver,Brushed,8223865195,2/13/2012,Site1,Agent1,YES ,NO
Wk 09,JKL,Gold , ,8223994403,2/27/2012,Site2,Agent2,YES ,NO
Wk 09,ABC,Gold , ,8223953204,2/22/2012,Site2,Agent2,YES ,NO
Wk 09,XYZ,Silver,Glossy ,8223903027,2/16/2012,Site2,Agent2,YES ,NO
Wk 10,DEF,Silver,Brushed,8223877375,2/14/2012,Site1,Agent1,NO ,NO
WK 10,GHI,Silver,Brushed,8223916154,2/17/2012,Site2,Agent2,NO ,NO
Wk 11,JKL,Silver,Brushed,8224212609,2/16/2012,Site5,Agent5,YES ,NO
Wk 11,XYZ,Silver,Glossy ,8224109504,3/7/2012 ,Site2,Agent2,YES ,NO
Wk 11,DEF,Silver,Glossy ,8228226143,3/7/2012 ,Site3,Agent3,YES ,NO
Wk 11,GHI,Silver,Glossy ,8224137660,3/9/2012 ,Site3,Agent3,YES ,NO
Wk 11,JKL,Silver,Brushed,8224175736,3/13/2012,Site4,Agent4,YES ,NO


Again thanks in advance....
and I will be out of pocket in an hour so I may not get back to this until later this evening or tomorrow. Then again, as much as I've slaved over this it might take you longer.. wait SKIP? Now Skip will snap his fingers and use his Skip-voodoo

Thanks!

ladyck3
aka: Laurie

 

[SkipVought]

So how do you determin with/without covers?

Skip,

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

 

[SkipVought]

Are the counts over the entire table or by week?

Skip,

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

 

[SkipVought]

Also it seems that there is a ONE to ONE relationship between Site and Agent. Is that by design or just a poor example?

Skip,

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

 

[ladyck3]

Just a poor example for site/agent There are 5 sites and MULTIPLE people per site and great chances of duplicate agents but they would belong to one site. Say Agent xyz works at site 4, he will only work at site 4.

I know I got confused trying to mask what all of this is for confidentiality sake so yeah.... I got the story a bit wrong I guess.

The criteria is basically whether or not this is a first order... the number of orders, which are first orders and the % of these first orders vs the number for that week.

There is another section that wants to know whether or not there were problems with any previous orders... these are the last 2 columns of the sample provided.

See what I mean? So much confusion.

It was written out for me like this:

Agent: # of orders > # of first orders > % of first orders vs ordered

By Site (sites 1-5) Same criteria


THEN another way of showing it....

Number of Gold Ordered > By Site > # of orders > # of first orders > % of first orders vs ordered


Number of Silver Ordered > Glossy > Brushed > by site > # of orders > # of first orders > % of first orders vs ordered



SEE MY CONFUSION??



ladyck3
aka: Laurie

 

[SkipVought]

trying to mask what all of this is for confidentiality sake

PT is not a good choice, IMNSHO, for maintining confidentiality. You might need to HIDE the source data and PROGRAMATICALLY access the data by agent USER ID, picked up from the SYSTEM logon value.

The criteria is basically whether or not this is a first order... the number of orders, which are first orders and the % of these first orders vs the number for that week.

Then you previously and subsequently IGNORE Week???

Still don't know what defines a COVER: with/without???



Skip,

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

 

[ladyck3]

the widget is ordered with or without a cover and if it has a cover it could be gold or silver and if its silver it can be glossy or brushed.

You know what? I'm more confused trying to explain it than I was before I started. Your questions are all valid I know that...

I gotta run right now, will think about this some more before pestering you...

The DOCTOR AWAITS!

ladyck3
aka: Laurie

 

[SkipVought]

the widget is ordered with or without a cover and if it has a cover it could be gold or silver and if its silver it can be glossy or brushed.

So the sample data you proveded is only WITH covers? You posted no data WITHOUT covers?

In order to test, we need an assortment of data that represents EVERY condition that you want to report.

Skip,

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

 

[ladyck3]

Well then randomly remove some of the gold and silver (brushed or glossy) entries so some of the fields are blank that would represent with and without covers

Are you still with me? I don't know if I am LOL

ladyck3
aka: Laurie

 

[ladyck3]

Here is the ULTIMATE DELIMA.... see the attached image.
The PERCENTAGE. Forget the story above and just focus on the percentage.

This hows Number of Orders, Number that are NTF and I need to know NTF percentage.

So I'm not educated on the differnet variable options in Pivot Tables... On the attached image the percentage is supposed to be the percenage of the total per week and any sub items should.. what I think would be, the percentage of the ROW but I can't seem to get that to work.

See Week 6... 71 tickets, 67 are NTF that would be roughly 94% of all tickets were NTF... but the calculation shows 32.x% because its OF THE TOTAL OF THE ENTIRE PIVOT TABLE, not of that group.

ARGH and this is the bottom line of what is causing me to go bald.. RIP RIP RIP more hair gone.

ladyck3
aka: Laurie

 

[SkipVought]

Remember, I cannot access your download.

Your original data has nothing related to NTF???

Need cogent examples in order to help.

Skip,

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

 

[SkipVought]

BTW, I tried 2 different @ tx.rr.com with failure. Was trying a direct.

Skip,

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

 

[ladyck3]

ummm verizon.net

ladyck3
aka: Laurie

 

[dhulbert]

Laurie

Where is the data stroed, is it all just in an excel sheet or is it in a database somewhere?
How many rows of data, how fast is it growing?
Are you trying to create a pivot tale for 1 user or are you trying to solve a numebr of issues with 1 pivot when really you need 2 or 3 for different people?
What version of excel are you using?



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)