Week/month/year rollovers with PivotTable

[Rayo K]

Hello,

I have a pivot table that summarizes production data on a monthly and weekly
basis. I have generated another table that uses the getpivotdata and displays
data for current month-to-date, previous month, week-to-date, previous week,
and each individual day of the current week. It works fine except for
instances where the week spans two months or two years.

I have solved the spanning two months by making a second Pivottable based on
the first that summarizes by WEEKNUM instead of month. However, the week
spanning two years is problematic (it seems I will be spared this Jan 1 but I
need a permanent solution). I thought of manually changing the year for
either the December days or the January days, but that will throw off the
Monthly calculation.

Any suggestions on how to resolve this.

 

[William Horton]

How is your source data laid out (what fields and what data do they contain)?
What are you considering to be a week (Mon - Sun, Sat - Fri, etc)? And when
should the first week of the year start on (1/1/year, first monday, etc)?

 

[Rayo K]

Thanks.

My raw data is laid out by date and shift (two shifts per day). I have my
production data in the first ten columns. I then have columns that using the
date functions to capture the year, month, weeknum, and weekday (using Monday
as 1) as integers.

All these fields go into the pivottables. The Month Pivottable arranges the
rows by year and then month (to get monthly totals). The Week Pivottable
arranges the rows by year, weeknumber, and then weekday (to get weekly and
daily totals). The Week Pivot uses the month pivot as its datasource.

I then have a final table that outputs the pivotdata in a specific format.

So my monthly totals are fine, as the first day of the year is the first day
of the month. But for example this year will end with week 53 having 6 days
and week 1 of 2006 having one day. Thus, on Jan 2, which will technically be
week 2, my pivottable will try to get the totals from the previous week and
will only come up with Sunday.

I should point out that I am no thte end user of this spreadsheet and that
the end users will not be proficient with excel. (I have gone as far as
creating a Button that uses VB to automatically refresh the Pivottable after
they enter the day's information.)

I hope this helps.

 

[William Horton]

Perhaps instead of using the WEEKNUM function you should just use the
individual dates (12/30/05, 12/31/05, 1/1/06, etc....). You can then use
Excel's group by functionality on the dates field. Right-click on the field
column heading and choose Group and Outline / Group. Then ensure that the
Days option is highlited and that the number of days is set at 7. You can
then adjust the starting and ending dates if need be in order to get the
weeks to fall in the proper places that you want.

Hope this helps.

Thanks,
Bill horton

 

[Rayo K]

Hmm..

This does not seem to work. When I click on the column field in the
pivottable, and try to group, it says "Unable to perform action on this
selection". I can group the individual dates manually, but the pivottable is
supposed to remain hidden. Am I doing the wrong thing?

I am now trying to figure out how to tell excel that December is the month
before January, even though 12 is more than 1. Any ideas on either problem?

 

[Rayo K]

Eureka!

I have a solution. I have created another source data field called week
serial that concatenates the year and week and assigns itself to dates based
on the weekday as follows:

IF(weekday=1,CONCATENATE(year,IF(weeknum<10,0,""),weeknum)*1,[yesterday's
week serial])

Thus every Monday, the week serial is regenerated, and the rest of the week
just copies that of the previous day. This creates a unique week identifier
that is not interrupted by the new year.

Thanks for all the suggestions!