Solved

Time Intervals on X axis PivotChart

Posted on 2011-09-30
5
1,068 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 (Microsoft MVP / EE MVE) 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

623 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