Greetings,
I have a pivot with dates, i am doing a group by 7 days to group my data by week.
It works well except, if there is no data for a week, it skipps that week.
When i try the option "show item with no data", it destroys the group by week and i cannot recreate it. Any way to have a "by week" WITH the empty weeks please?
Regards,
can you post your workbook?
Blake 7
If your question has been answered, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If any member's response has helped to solve your problem, please consider using the scales icon on the top right of their post to show your appreciation.
http://failblog.org/
Hi jhaquo and welcome to the forum,
I've solved similar problems by putting in a row of data with the missing day but zero or blank for data on that day.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi and thanks for the quick replies.
The data comes from an ms sql server, so i cannot simply add rows with 0
i cannot share the worksheet either cause its a lot of cofidential stuffsorry
but feel free to ask anything
don't you export to excel from the database?
You can fill the blanks as Marv suggested once its exported, then insert the pivot
Can you add a week number in another column of your data and group by that?
=WeekNum() ?
WeekNum is better for grouping because if you group by day and start on a Tuesday, the Pivot will simply group every 7 days from the starting day. With WeekNum it will use the Weeks of the Year and you won't need to worry about the starting day for the grouping.
Last edited by MarvinP; 03-23-2011 at 09:58 AM.
Hi Marv - great post. wasn't aware of that so thanks !! learn something every dayOriginally Posted by MarvinP
thanks Marvin, its pretty awsome indeed. one of my collegues had an isoweek function created on the mssql server. so i have my nice field with the isoweek calculated (EU here, different week numbers).
now, my problem is:
if i do no check "show items with no data" , i still does not display the weeks with nothing (which is normal) and if i check it, it shows the 53 weeks.
can i limit the weeks between two dates?
example: show all the weeks in the month january and february?
using an easy way, not checking which weeks those are and selecting them in the drop down...
thanks allready for all the help guys
In the newer version of Excel there are date filters (and a lot more of them), but I'm not exactly sure about the filters in 2003 Excel for dates.
One Test is worth a Thousand Opinions = try it and find out!
i'm on 2010 actualy (didnt update the profile) but the end users will be on 2007... any direction to point me? beside the door :P
Look at the Date Filter options on 2010 - you get "Last Month", "This Month" and a bunch more pre-defined date filters. The 2007 version has less selections but most can be duplicated with some work.
My direction is what we all do. Keep studying and learning.
Look at http://www.pivot-table.com/ for a lot of things you didn't know were in them.
I've still got to wrestle with Calculated Items and Calculated Fields.
Hey J - you may find this helpfull.
htttp://www.cpearson.com/Excel/WeekNumbers.aspx
@ jhquao,
Your post is very helpful for when I get to that point, but I am trying to do what you did first: grouping data from days into weeks. The weekday function is the only one I can find. Weeknum doesn't work for me (I have excel 2003, don't know if that's a problem).
Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks