Pivot Table - Adding 2nd Running Total Column 1

[beadedbytes]

I have a pivot table that has a running total grouped by week (see column below labelled as "Cum of Qty").

Data
DATE BY WEEK Sum of Qty Cum of Qty
7/2/2006 - 7/8/2006 196 196
7/9/2006 - 7/15/2006 198 394
7/16/2006 - 7/22/2006 399 793
7/23/2006 - 7/29/2006 910 1703
7/30/2006 - 8/5/2006 278 1981
8/6/2006 - 8/12/2006 284 2265
8/13/2006 - 8/19/2006 347 2612
8/20/2006 - 8/26/2006 1071 3683
8/27/2006 - 9/2/2006 462 4145

I would like to add another running total column that describes '7/2/2006-7/8/2006' with a value=week1; '7/9/2006-7/15/2006' with a value=week2; '7/16/2006-7/22/2006' with a value=week3, etc.

Is there any way to have this 2nd running total inside the pivot table?

 

[GlennUK]

I can't see that what you are describing is a running total. Do you just mean that you want different titles for these weeks?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[beadedbytes]

Glenn--
In addition to the existing 3 pivot columns (Dt by Wk {grouped}, Sum Qty, Cum Qty{running total}), I would like to add a 2nd 'running total' that contains cumulative week count (see column named 'Week#' below). Usually after creating the pivot table, I manually enter the 'Week#' column to the right of the pivot table area.

Let me know if you have any other questions.


Data
DATE BY WEEK Sum of Qty Cum of Qty Week#
7/2/2006 - 7/8/2006 196 196 wk1
7/9/2006 - 7/15/2006 198 394 wk2
7/16/2006 - 7/22/2006 399 793 wk3
7/23/2006 - 7/29/2006 910 1703 wk4
7/30/2006 - 8/5/2006 278 1981 wk5
8/6/2006 - 8/12/2006 284 2265 wk6
8/13/2006 - 8/19/2006 347 2612 wk7
8/20/2006 - 8/26/2006 1071 3683 wk8
8/27/2006 - 9/2/2006 462 4145 wk9

 

[KenWright]

Just add another field to your source data, and use a VLOOKUP against a separate table of dates and associated week Numbers to populate it based on whatever is found in the date column in your source data. Then just drag it into your ROW fields and lose the totals that it puts in.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[beadedbytes]

Ken -
Thanks for the feedback.

To date, I have tried doing something similar to what you described. However, the example I used in this post is one of many scenarios that I create on a regular basis.

Also, the start 'week1' will also vary for each new scenario created. That's why I defaulted to doing it manually after creating the pivot table. It just seemed simpler!

 

[SkipVought]

Hi,

Here's a way to calculate the week number in your source data.

Assuming that the dates are in column A...
[tt][highlight white]
=INT((A2-[red]1[/red])/7)-(INT((MIN(A:A)-2)/7)-1)
[/highlight][/tt]
play with this [red]number [/red] to adjust the starting day of week. It assumes that the MIN date in Column A, is in Week 1.

Skip,
[sub]
[red][/red]
[/sub]

 

[beadedbytes]

IT WORKS! Thank you very much!

One last question ... While I understand the definition of INT function, admittedly I don't understand how this formula works. Would you mind providing an explanation in layman's terms?

Eternally grateful, Skip.

 

[beadedbytes]

Hi, Skip,
I just discovered an issue.

When I use this formula in my source data and use the result as a row field of the pivot table, it changes my running total to just the sum total for the week in question. For example, the 'cum of qty' for week 7/30/2006 - 8/5/2006 changes from 1981 to 278. Any thoughts as to what may be happening?

 

[SkipVought]

It may depend on the day the week break occurs. It could throw values into a different week bucket. You need to coordinate the Group START DATE with the WEEK start date in your forumla.

The formula does this...

Note: Dates are NUMBERS, like Today is 39225.

Take ANY sequence of numbers, divide each by any other number, in this case 7 (days in a week), throw away the remainder (that's what INT does) -- this is a week group number, in your case. Forthermore, we take the MIN date, do the same thing to it, subtract one from THAT START WEEK group number and then subtract the START WEEK value from the week group number in order to start at Week 1.


Skip,
[sub]
[red][/red]
[/sub]