Weekly average in pivot

[Guest]

I'd neet to get weekly averages in a Pivot Table. The source data may contain
several lines for one week. How can I get AVG weekly volume/customer/quarter
from this kind of data?

Any advice is greatly appreciated!

Customer Volume Origin Year Q Month Week
XXX 3000 A 2007 1 1 1
YYY 1000 A 2007 1 1 1
ZZZ 150 A 2007 1 1 1
XXX 350 A 2007 1 1 2
XXX 4000 L 2007 1 1 2
YYY 100 A 2007 1 1 2
YYY 800 L 2007 1 1 2
ZZZ 100 A 2007 1 1 2
ZZZ 150 L 2007 1 1 2
XXX 1000 A 2007 1 1 3
XXX 3000 L 2007 1 1 3
XXX 150 T 2007 1 1 3
YYY 500 A 2007 1 1 3
YYY 400 L 2007 1 1 3
ZZZ 200 L 2007 1 1 3
XXX 3500 A 2007 1 1 4
XXX 700 L 2007 1 1 4
YYY 1000 A 2007 1 1 4
YYY 500 L 2007 1 1 4
ZZZ 200 A 2007 1 1 4
XXX 3500 A 2007 1 2 5
XXX 300 L 2007 1 2 5
YYY 300 A 2007 1 2 5
YYY 1800 L 2007 1 2 5
ZZZ 50 A 2007 1 1 5
ZZZ 150 L 2007 1 1 5

 

[Roger Govier]

Hi Max

In the PT
Drag Customer to Row area
Drag Q to Row area
Drag Week to Row area
Double click on these fields and set Subtotal to None
Drag Volume to Data area
Double click on Volume and change Sum to Average

 

[Guest]

Thank's Roger,

Unfortunately I wasn't looking for this, it returns the average of the
individual cells. What I need is an average of the weekly volumes for each
month/quarter/year (regardless of origin in the example).

-Max-

 

[Roger Govier]

Hi Max

I don't understand.
With your data, I get a value of 1383.33 for Customer XXX, Q1, Wk3.
This is the Average of 150 from Origin T, 3000 from Origin L and 1000
from Origin A

Perhaps you could explain what you are wanting in more detail.

 

[Guest]

Hi Roger,

Let me clarify. I'm looking for the customer's volume per week. For the week
it's easy, it's just the aggregated sum of all origins. Then I need to have
the customer's average for the month (and quarter and year). In this report I
don't care about the origin at all, it's just the way the data is recorded in
my source database (and the cause of my current problem).

Would it be a solution to use the firdt PT with sum fo volume as source for
a second PT with avg of the fist PT. Can I define a PT as source for another
PT?

-Max-

 

[Roger Govier]

Hi Max

Double click on the Quarter field >Subtotals>Custom>average.
Do the same for the Annual field.

 

[Guest]

Thank's again Roger,

I appreciate your attention to my problem. Unfortunately the average
function always seems to calculate the average of all the underlying data
cells. It's dividing the total by the number of data cells when I'd want to
force number of weeks to be the divider.

-Max-