Solved

Time Intervals on X axis PivotChart

Posted on 2011-09-30
5
904 Views
Last Modified: 2012-06-21
Hi

I have a pivot chart w/ an x axis w/ times.

Example

4:30, 4:31, 4:32, etc.... going all the way up to 10pm

I want to know how to make it look clean

Right now its it trying to display every value which looks liek way too much on a graph.

If i group by hours it doesnt really do the trick. Maybe i can group by 15-30 min intervals?

Any suggstions?
0
Comment
Question by:kwarden13
5 Comments
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36892725
Could you please attach a sample?
0
 
LVL 8

Expert Comment

by:WileECoyote45305
ID: 36894115
Look at this article on how to use timescale values on the X-axis - one example for dates, and one example for times:
http://www.mrexcel.com/articles/excel-time-series-chart.php

I tried the procedure for the time values and was able to successfully set up a clean chart with time values on the x-axis. I used 15 minutes as the Major Unit (0.01042) and let it auto-pick the Minor Unit on the Scale tab. Use the directions to help determine the values for Minimum and Maximum based on your range of time values.

So although I had data for times such as 4:30, 4:31, 4:52, 5:05, 5:12, 6:27, 7:09 and 10:00, I had a chart with x-axis labels of 4:00, 4:15, 4:30, 4:45, etc. all the way to 10:00 and all the points plotted properly.
0
 
LVL 8

Expert Comment

by:WileECoyote45305
ID: 36894133
My comment was for controlling the x-axis labels. If you really do have a data point for every minute, what I did doesn't address all the plotted values in the chart, but cleans up the x-axis. Sorry if I misunderstood.
0
 

Author Comment

by:kwarden13
ID: 36894158
Attached is a sample
TestBook.xls
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 36896201
Hello,

does that sample data span several days? The cells in column A show only time values and they seem to be in roughly hourly increments, around 23, 24 or 29minutes after the full hour. Formatting the Time values with General, though,  I can see that some of them have a 1 in front of the decimal, others have a zero. In Excel terms that means that some time values are on a different day than others. Some have no date, some have a date of 1/1/1900.

Is that the intention? Before you can do any meaningful pivot analysis, you will need to clean up your data. There either should be a date value with EVERY time value or with NONE.

If you create a pivot table of that data and want to group in 15 or 30 minute increments, then you will need data that fits that pattern. The sample data comes in hourly increments. What good would 15 or 30 minute increments do?

So, either post a sample set that is more representative of your real data, explain some more what the data is supposed to portray and how you want to summarise it.

You may need to use a helper column in the source file to create a meaningful pivot chart, but that can only be determined if you provide some more info on what the data shows and how you want to summarise it.

For example: You can have several data items per hour for a series of days. The pivot chart should disregard the date and just summarize the data by its time slot, so you see a statistic of values typical for the hour of the day (summing up all data for 9am, 10am, 11am, etc, across ALL days). In this case, the X axis would only show the time and the lines would be the sum (or average) across all days.

Or: You have several dozen data items per hour for a series of days. The pivot table should show the data in a time line, but summarize data in 15 minute blocks per day. The X axis would show each day, broken down into time slots.

Which of these do you want to show?

cheers, teylyn
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Outlook Free & Paid Tools
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question