Pivot Table Puzzle

[#1]

I am super frustrated with this project at this point [:@] and need some help.

I have a Pivot table with the following dims (CustomerGroup, Origin, PickUpWeek)

Currently the expressions are as follows

Trips= Count(OrderNo)

5wk Average= GetSelectedCount(PickupWeek)

Note: Currently 5 weeks are selected but obviously this changes as more or less are selcted

The Table looks like this (See Lowest Example).

What i need is for the 5 wk average row to actually be a 5wk average, i.e. The Customer "C Valley"

Should have a an average of "9.0" (see total rows 45/5= 9.0) I would like this number to appear in all the cells below the Trip Count for that week and also a variance below them both i.e.

Customer..Origin...PickUpWeek 7/26/09....7/19/09..........

(CValley....ARZ......Trips....................10.................9..............

(CValley....ARZ........5wk Avg................9.................9..............

(CValley....ARZ........Variance...............1.................0..............

Anyone have any ideas? You would be a life saver!!!!!

[;)]

CustomerGroup	OriginPOD	PickupWkStart	7/26/2009	7/19/2009	7/12/2009	7/5/2009	6/28/2009	Total
ATS	ARZ	Trips	0	3	2	0	0	5
ATS	ARZ	5 Wk Avg.	5.0	5.0	5.0	5.0	5.0	5.0
C VALLEY	ARZ	Trips	10	9	8	8	10	45
C VALLEY	ARZ	5 Wk Avg.	5.0	5.0	5.0	5.0	5.0	5.0
CHIQ	ARZ	Trips	0	0	0	1	3	4
CHI	ARZ	5 Wk Avg.	5.0	5.0	5.0	5.0	5.0	5.0
COST	ARZ	Trips	2	2	1	1	4	10
COST	ARZ	5 Wk Avg.	5.0	5.0	5.0	5.0	5.0	5.0

[#2]

could you upload your QV ?

[johnw · Accepted solution]

This, maybe?

count(total <CustomerGroup,OriginPOD> OrderNo)/5

[#4]

I can not up load the qv at this time but i can tell you hat i have adjusted so far.

Keeping the same dimensions

I changed the Wk Average expression to "=avg(ALL aggr(sum(TripCounter), CustomerGroup))"

This is giving me what i want if i only have one CustomerGroup selected:

CustomerGroup	OriginPOD	PickupWkStart	8/9/2009	8/2/2009	7/26/2009	7/19/2009	7/12/2009	7/5/2009	6/28/2009	6/21/2009	Total
C VALLEY	ARZ	Trips	9	10	10	9	8	8	10	8	72
C VALLEY	ARZ	8 Wk Avg.	72.0	72.0	72.0	72.0	72.0	72.0	72.0	72.0	72.0
Total		Trips	9	10	10	9	8	8	10	8	72
Total		8 Wk Avg.	72.0	72.0	72.0	72.0	72.0	72.0	72.0	72.0	72.0

But if i have more than one it goes bad again and takes the total average of all the Customer Groups:

CustomerGroup	OriginPOD	PickupWkStart	8/9/2009	8/2/2009	7/26/2009	7/19/2009	7/12/2009	7/5/2009	6/28/2009	6/21/2009	Total
C VALLEY	ARZ	Trips	9	10	10	9	8	8	10	8	72
		8 Wk Avg.	30.3	30.3	30.3	30.3	30.3	30.3	30.3	30.3	30.3
CHIQ	ARZ	Trips	0	0	0	0	0	1	3	2	6
		8 Wk Avg.	30.3	30.3	30.3	30.3	30.3	30.3	30.3	30.3	30.3
COST	ARZ	Trips	0	0	2	2	1	1	4	3	13
		8 Wk Avg.	30.3	30.3	30.3	30.3	30.3	30.3	30.3	30.3	30.3
Total		Trips	9	10	12	11	9	10	17	13	91
		8 Wk Avg.	30.3	30.3	30.3	30.3	30.3	30.3	30.3	30.3	30.3

There has to be something i can add to the AGGR statemnet that will make it so it totals and averages each idividually.

Any ideas?

[#5]

Just a little tweeking for what i needed and it worked like a charm:

=count(total <CustomerGroup,OriginPOD> OrderNo)/GetSelectedCount(PickupWkStart)

I madeit a lot harder than it had to be.

Thanks for your help!!