Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
could you upload your QV ?
This, maybe?
count(total <CustomerGroup,OriginPOD> OrderNo)/5
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?
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!!