Excel Graphic Line Chart

Question

I have a data set (~ 10,000 rows) with the following form:

+---------------------------+---------------+-------------+ | DateTimeCreated | Machine | ProductName | +---------------------------+---------------+-------------+ | 2009-03-03 00:00:12.217 | COMP001 | Product001 | +---------------------------+---------------+-------------+ 

I did this using a pivot table and chart with the following layout

 Page: ProductName Column: Machine Row: DateTimeCreated - Group by Day, Hour, Minute Data: Count of DateTimeCreated 

This all works correctly, but the time frame of the resulting graph is not linear. For those minutes of the day when the event that I am tracking does not happen, these minutes are not displayed on the chart. The event that I am tracking can also go without hours, which leads to an even more non-linear schedule.

Here is an attempt to illustrate the x axis of my graph:

 |09| 10 | 11 | 12 |14| 16 | 18 | 20 |23| 

Desired Result:

 |00|01|02|03|04|05|06|07|08|etc 

So, every hour is the same size and displayed even if the event did not happen (so on my line chart the line just goes to 0 for a while)

Can this be achieved?

Answer 1

I changed my dataset to look like this

 +---------------------------+---------------+-------------+-------+ | DateTimeCreated | Machine | ProductName | Count | +---------------------------+---------------+-------------+-------+ | 2009-03-03 00:02:00.000 | COMP001 | Product001 | 2 | +---------------------------+---------------+-------------+-------+ 

so that the request groups events by minute and provides an invoice per minute

Then I generated a second dataset containing a record for every minute of the day, but with the number 0 Since I only worked with 24-hour datasets, this was not unreasonable.

Combining the two datasets gives a linear timeline in the pivot chart.