[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

Pivot tables response time website

Hi,

I urgently need to convert this xls into a graph.
It is the responsetime of a website from one month on a 5 minute basis. The graph should show date, time and response time on that time.
Don't know where I get it wrong but doesn't seem to make it work.

Thanks for your help!
J.
responsetimes.xls
0
janhoedt
Asked:
janhoedt
  • 2
  • 2
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
I suggest inserting a new column and combining the date and time values in that column as your new time (x) axis.

Additionally, after the chart is created, the x-axis will need to be changed to a text axis.  It will default to a date axis and will only show points on each calendar date, not by the h:mm value.

Also note that you're plotting more than 6,000 points, this will take time to process each step.  I recommend graphing a smaller set (maybe first thousand points) first, make all the formatting changes to the graph that you want, then extending the data range.

I've attached an example file with a couple of graphs. responsetimes.xls
0
 
janhoedtAuthor Commented:
Thanks! But now I would like to see a line instead of the confusing graph.
It's a response time of a website, responsetimes are in milliseconds (not seconds as in your Excelsheet) and are taken from different locations. Actually 1 location would be sufficicent + a check every hour would be ok. Do you know how to extract this from the data without manually editing each line?

Note: do you know also a dummy-guide for pivot-tables?
Cheers,
J.
0
 
Glenn RayExcel VBA DeveloperCommented:
I think the graph is pretty self-evident.  It's very obvious to me that, save for about a half-dozen instances where the response maxes out (30 seconds), the overall response time gradually increased from June to October until abruptly dropping back down to the pre-June levels.  New server maybe?

Your original data labeled the response time as "seconds", although, I should have surmised that the response time was in milliseconds!  

With the number of plot points shown, it makes more sense to not show lines connecting the points:  the line implies a trend between points (e.g., a rise in temperature) rather than with discreet measurements, such as shown here.  It also takes much longer to plot.

If you want to include a trend line, you can do that in the graph simply by right-clicking on the data and choosing "Add trendline..." and choosing an appropriate analysis.

RE: reducing data set
Your original data does not show a location for each row, so that would not be possible,  
Also, your original data does not have a consistent timestamp (ex. every five minutes), but one could add another column that tests for "top of the hour" results (between 0 and 4 minutes after the hour) and plot that instead (I've attached a modified file that does just that).

RE: Pivot Tables
I can't recommend any specific introductory guide for using Pivot Tables.  Any good Excel book should do.
 responsetimes.xls
0
 
janhoedtAuthor Commented:
Great, thanks!
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now