• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

pivot chart date formatting

M

maku

New Member
Does excel have a limitation to show dates format if data column has mixed entries?


I have a pivot table and chart that has months listed. (year is a separate column).

I've tried every way I can think to format the months column (text, date, general, etc) to get the end result on the pivot chart to just show mmm (ie- Jan Feb Mar)


I can format the pivot table to achieve this, but when I click on the format axis, format number and every other option I can find on the pivot chart, the dates revert to 1/1/2012, 2/1/2012 3/1/2012.


Here's where I think the problem may be at. In my Month column, I also have 1st Qtr, 2nd Qtr, 3rd Qtr, and 4th Qtr entries. Is this what is preventing the date from formatting? Any work arounds to also input quarterly data?


Thanks,

-Maku
 
Maku


It sounds like you have a combination of Dates (numbers) and Text (1st Qtr etc) in your date column.


Can you post a file with a sample of your data (Good and Bad) and a list of what you require.


Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hui...thank you for offering to take a look. The workbook is still a work in progress, so not all is functional, but most is.


The pivot charts on the "Report Card" worksheet (ws) are located in the PivotTable ws. THe PivotTable data comes from the MasterData ws. The instruction page tells how I got the MasterData ws.


I'm trying to achieve the following:

1) Format the Dates on the Pivot charts as "mmm" or Jan Feb Mar; so they appear as they do in my Pivot Tables.

2) If I need, reformat the "PERIOD" or "MONTH" columns in my property worksheets where this data is coming from to achieve goal #1 above.

3) Create a second pivot chart filter variable that will change the period selected.


Let me elaborate on #3. I have a the pivot charts setup to dynamically change based on the property selected on the Report Card ws from the drop down list. It works, but I also want to figure out how if I select 2nd Qtr from the Report Card ws, that the pivot chart will select not only the 2nd Qtr, but also the months associated with that quarter (Apr May Jun).


Thanks and I look forward to any feedback.


-Maku


http://speedy.sh/qBkue/dynamic-report-maku.xlsm
 
Hi ,


Modify your original data ; column C in the tab labelled Master Data can be modified to have the following formula :


=IF(B2>DATEVALUE("01/01/1900"),TEXT(B2,"mmm"),B2)


With this change , refresh your Pivot Table , and you should get the Horizontal Axis of the Pivot Chart formatted as Jan , Feb , ...


I would like to make two unrelated comments :


1. You have a tab labelled Master Data , and you also have tabs for each property ; I find that all these tabs have data in the form of entered values ; I would have thought that if the Master Data tab were to live up to its name , then data in all other tabs should have formulae to retrieve values from the Master Data worksheet.


2. To calculate the quarterly totals , you have used SUM ; it would be better to use the SUBTOTAL function , since this automatically takes care to ignore nested SUBTOTALs.


Narayan
 
Narayan- Thank you...your formula worked to change the data formatting! I couldn't figure out how to get that to change...I'm amazed at the knowledge on these forums.


Regarding your comments, I think the structure is opposite of what you are thinking in your comment #1. The data in the tabs have raw data and formulas that have calculations based off raw data to derive key figures. The MasterData worksheet is simply raw data values for my pivot charts. The MasterData table/sheet is compiled from a macro - CombineMasterData.


I will work on the quarterly data using SUBTOTAL. Do you know if it's possible to setup a second filter on the pivot chart that will detect if a Quarter of the year is selected, that the pivot table selects the three months with that selected Quarter?


Thanks for getting me a step closer :) -Maku
 
I found I could group months on the pivot table and name that my quarter [ http://chandoo.org/wp/2009/11/17/group-dates-in-pivot-tables/ ]


But, when I do this, if I unselect my data entry 1st Qtr in the pivot table/chart and just leave my group 1 showing which shows (Jan Feb Mar) I unfortunately lose my Quarter sum totals chart. If I select and include this in the list, then if I collapse the Group 1 it sums up Jan Feb Mar and 1st Qtr which in effect doubles the result.


Is it possible to to display Jan Feb Mar and 1st Qtr amounts in expanded view and just 1st Qtr amounts in collapsed view?

thanks-Maku
 
No worries...family and friends are more important than excel formulas :) I have other things to work on and can modify this when/if the answer comes forth.
 
Hi ,


What I could get from the Internet is that subtotals and grand totals cannot be displayed on a Pivot Chart. Check out these links :


http://www.pcreview.co.uk/forums/q-including-subtotals-pivot-chart-t3139885.html


http://peltiertech.com/Excel/Pivots/pivotcharts.htm


What is suggested is that you create a normal chart ( not a Pivot Chart ) from the Pivot Table. Hope this helps.


Narayan
 
That's a downer. I'm glad I found the grouping option. I'm hoping to develop this further with Running YTD totals with Charts in the future. I may have to use a standard chart and not pivot chart.


THanks Narayan!
 
You must log in or register to reply here.
Back
Top