Line Graph with Date on X axis and occurrence on X axis

Question

I am trying to produce a Line graph that shows the number of access requests over a given time period to my server.

The log data is like so:

• 10/05/2013
• 10/05/2013
• 10/05/2013
• 11/05/2013
• 11/05/2013
• 14/05/2013
• 14/05/2013

Sp the graph would show on the 14/05/13 there was 2 rows in the spreadsheet.

How do I represent this in a line graph.

The column is "D" where all my dates are held.

Thanks

Answer 1

Hmm, you could do a pivot with this data, or insert a column which will have the number of occurrences of each date.

Pivot table

1. Be sure to have a column name and then do a PivotTable on the field D (Go to "Insert" > "PivotTable" > Select your data and then "OK").

2. Drag the field into "Row Labels" and "Values" both.

3. You can then insert a line graph from the pivot table itself.

Adding a new column

Add a column to the right of the dates containing the formula (I assume that D1 contains the column name and that the data starts in cell D2):

=COUNTIF(D:D,D2)

Name the column (e.g. Number of access requests) and insert a line graph based on the two columns.

Answer 2

If this data will need to be updated on a regular basis, a Pivot Table fed by a Table is the way to go.

1. Convert your data into a Table (Insert>Table). This will update whenever you add data. Also, if your data is coming from an ODBC compliant source (e.g. SQL Server), you can have it automatically update with current data.
2. Add a column to your Table, called Count. Then enter the formula =1. This will automatically fill a 1 in for every value in your Table.
3. Create a Pivot Table (Insert>Pivot Table) using your Table as a data source.
4. Format your Pivot Table like this (it will be similar to Jerry's Pivot Table):
   • Row Labels = Date
   • Values = Count
5. With your Pivot Table highlighted, create a Line Chart (Insert>Line). This will create a Pivot Chart which will automatically update with your Pivot Table.

When you're done with this setup, everytime you add a value to your primary data Table, you can refresh your Pivot Table and the associated Pivot Chart will also update.

Answer 3

I think what you need is a step line chart based on your description. To draw this kind of chart, I propose you to use the free Funfun Excel add-in, which allows you to use JavaScript code directly in Excel. The step line chart would be easy to draw using JavaScript. Here is an example that I drew based on your sample data.

Basically, the Funfun Excel add-in enables you to use JavaScript to process the data you stored in the spreadsheet. That being said, you could use libraries like HighCharts.js or D3.js to draw charts. In this example, I used HighCharts.js. For your sample data, there is not always logins for each day. If you don't give a data (0 logins) for dates that without logs, you line will not show correctly. What I did in here is to create an array with all the dates in the month, each date will have a default 0 logins. Then based on your logs in the spreadsheet, I compare their date with the pre-created array and add logins into the array. Here is some code.

var dayFirst,
      monthFirst,
      dayLast,
      monthLast;

  dayFirst = parseInt(data[1].split('/')[0]);
  monthFirst = parseInt(data[1].split('/')[1]) - 1;
  dayLast  = parseInt(data[data.length-1].split('/')[0]);
  monthLast = parseInt(data[data.length-1].split('/')[1]) - 1;

  for(var i=monthFirst;i<=monthLast;i++ ){
    for(var j=dayFirst;j<=dayLast;j++){
        logins.push(
            [Date.UTC(2017, i, j),0]
        );
    }
  }

  for(var i=1;i<data.length;i++){
    var dateThis = data[i];
    var year = parseInt(dateThis.split('/')[2]);
    var month = parseInt(dateThis.split('/')[1]-1);
    var day = parseInt(dateThis.split('/')[0]);

        for(var j =0;j<logins.length;j++){
            if(logins[j][0]==Date.UTC(year, month, day)){
                logins[j][1] += 1;
            }
        }    
  }

The Funfun also has an online editor in which you could explore your JavaScript code and result. You could check the detail of how I made the example chart in the link below.

https://www.funfun.io/1/#/edit/5a4e571b1010eb73fe125cd9

Once you are satisfied with the result you achieved in the online editor, you could easily load the result into your Excel using the URL above. But of course, first, you need to add the Funfun add-in into your Excel by Insert - Office Add-ins. Here are some screenshots showing how you load the example into you Excel.

Disclosure: I'm a developer of Funfun