Year over Year - Dealing with Weekly data

This is a weird question that I don't know if it particularly pertains to charts and stuff but I was not sure where to ask it.

I am making a huge report that is Year over Year and it has weekly data for many attributes. I've gotten the pivot table, chart, etc all figured out - the only thing I am stuck on because I have not done full year over year data before is how to handle the "weeks" without losing some of the data between years.

For example in my mock up project I was just using the =WEEKNUM formula based on the date to give me week numbers for my data sets. I used this in all my reports based on date so it would be uniform, so that is working. But what happens is at the end of a year because years/months end in the middle of weeks is there is usually a weird fall off for January where week one has only 2 or 3 days and so that week one has low values for all the attributes.

Is there some proper way to handle this or do you just deal with it knowing that first week is an "anomaly"? I assumed there was some kind of method to include data from the previous years last week into that first week but that would not be accurate either so maybe you are just supposed to deal with that strangeness.

Sorry for such an odd question, if there is a better forum for this pls do move the post =D

NewYears,

Answer depends on what the End User wants?
I've had client who "split" first and last weeks (e.g. Week 1 is "Total of 'equal to, or more than, whatever day 1 Jan is', and 'less than or equal to whenever the Saturday is' (assuming your weeks are Sun - Sat?)
Others have included days outside the Calendar year, and accepted that means "Double Counting".

Ochimus

Originally Posted by Ochimus

NewYears,

Answer depends on what the End User wants?
I've had client who "split" first and last weeks (e.g. Week 1 is "Total of 'equal to, or more than, whatever day 1 Jan is', and 'less than or equal to whenever the Saturday is' (assuming your weeks are Sun - Sat?)
Others have included days outside the Calendar year, and accepted that means "Double Counting".

Ochimus

Yeah that's kinda what I was hinting at. Right now the project I am working on is a proof of concept. It's a huge dashboard line graph (PivotChart) that has a ton of different data in it based on weekly numbers and is year over year.
The issue is using the =WEEKNUM function in Excel I actually get weeks 1-53. Week one is usually just Jan 1 and 2 as you stated. So the graph is GREAT except the start and end of the year is "wonky"
I need to consider doing what you just stated just have to figure out how to do it.

Right now I have 6 different reports that poll different types of data and one of hte issues is some of these reports use different week formats (IE Google report might be Friday - Sunday, while another may be Sunday-Sat).
That's why I for now I used WEEKNUM based on the date so that at least all the data lines up universally, the only weird thing is there's one report where someone else presets the weeknumbers already and they use a Mon-Friday..so it kind of messes up that data.

All new to me - been fun process just some wonkiness. Would be nice if the calendar weeks were always Sun-Sat and we just had exactly 4 weeks in a month - lol.

NewYears,

Just caught up with your last post. If you can get back to the 'actual' dates the different reports use as the week's span, you can use the Macro to standardise them all to whatever weekly format you want.

Be interesting, of course, if you have a Middle Eastern operation where Sat - Wed are working days whilst Europe and the States run Mon - Fri!

Ochimus