Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Time Intervals on X axis PivotChart

Posted on 2011-09-30
Medium Priority
1,125 Views
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
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

LVL 10

Expert Comment

ID: 36892725
Could you please attach a sample?
0

LVL 8

Expert Comment

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

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

ID: 36894158
Attached is a sample
TestBook.xls
0

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 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

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
###### Suggested Courses
Course of the Month6 days, 19 hours left to enroll