I am pretty new to pivot tables but I did create the attached pivot table,
For some reason in the "Sales by Month" Pivot table it doesn’t recognize it as a Date format only as a Text format as it doesnt sort by date, I assume its because of the function in the “Month&Year” column in sheet, please advise what I am doing wrong,
Thanks in advance.
I added a new colum with the formula =month(a1) in it.
I formatted it as 00
After that i made an pivot table.
Do you have question or comments, just ask or comment.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Thanks, but this doesnt work for me as I need the the row labels should be the customer names,
Maybe this way?
Thanks, yes this sounds great,
Question: what do you mean with "I formatted it as 00" in the first reply, how did you do it?
Cellproporties (last option) and the instead of standard use 00
Thanks, I really appreciate your help,
How about having the name of the month, for example Jan, Feb, instead of the number, and then to be able to sort it by month?
Then you get april (letter A) first, that's why I used 01 - 12 instead of text.
so what I am understading is that if I want to sort it by month (oldest to newest) we have to use numbers instead of text?
Hi mb1074
If you group your dates, there should be no need for the extra column.
See the attached, is that of any help!
Regards Kevin
Merged Cells (They are the work of the devil!!!)
Thanks Kavin, this sounds much better,
If I may ask, how do I group the dates?
Thanks again,
Hi mb1074
Right click one of the months in the pivot table, select ungroup. Then right click one of the dates, then ensure the months is selected, click OK.
Thank you soo much, this works great,
BTW, was there a need to create a year column to be able to group by year and date, or can it be worked out from the transactiondate column itself?
I'm not sure on that. If there is a way of having another Transactiondate field, you should be able to get the years. But I do not know how! Sorry, perhaps someone will come up with a answer. Interesting though, will have to look into that.
Kevin,
Why when I follow your steps on grouping by month in my actual spreadsheet there is a message "Cannot group that selection."?
Last edited by mb1074; 01-18-2013 at 01:24 PM.
Hi
It works over here. See the picture.
Sort by Month and year in pivot table.PNG
Hi mb1074
See the attached. i have deleted both your Year & the Month/Year columns in your data table. When you group the Transactiondate field, select both year & months for groups. Then have the Transactiondate field for column labels (Months) and the new year group for row labels.
See the attached sheet.
Last edited by Kevin UK; 01-18-2013 at 02:52 PM. Reason: Updated Pivot Table
Thanks a million, Kevin,
I ran again my report, it works great, i marked this thread solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks