I have a Pivot of monthly data. For a subset of my team, I need to use weekly data. I have the Week data as a row label under Month:
Sum of Sales
Agent's Name
...1 (Month)
.......2 (Week)
.......3 (Week)
.......4 (Week)
.......5 (Week)
.......6 (Week)
...2 (Month)
.......6 (Week)
.......7 (Week)
.......8 (Week)
.......9 (Week)
.......10 (Week)
The problem comes in at Week 6. Because it is split between Month 1 and 2, I cannot get it to return the total value.
I of course cannot simply order the data by Week number, because I'd encounter the same problem with my Monthly data.
I'd like to avoid having to create a second pivot, due to the size.
Below is the stripped down formula. Can anyone make it work? Or help me in a different direction to the same result? Thanks!
Sum of Sales
Agent's Name
...1 (Month)
.......2 (Week)
.......3 (Week)
.......4 (Week)
.......5 (Week)
.......6 (Week)
...2 (Month)
.......6 (Week)
.......7 (Week)
.......8 (Week)
.......9 (Week)
.......10 (Week)
The problem comes in at Week 6. Because it is split between Month 1 and 2, I cannot get it to return the total value.
I of course cannot simply order the data by Week number, because I'd encounter the same problem with my Monthly data.
I'd like to avoid having to create a second pivot, due to the size.
Below is the stripped down formula. Can anyone make it work? Or help me in a different direction to the same result? Thanks!
Code:
=GETPIVOTDATA("Sum of Sales Count",$A$5,"Agent",BM5,"Week Num","6")
