Chart Data for Open and Closed ticket totals and dates

[LucyK]

Hi

I was wondering if someone would be able to help me with a workbook chart issue. I am trying to represent open and closed dates on a chart effectively charting the trend of when something opened versus how long it took to close by month. but my excel chart doesn't seem to present that data effectively. Example Picture:

My actual Data is much larger but is restricted to my work laptop I have approx 350 rows of data to try and display in the form of a chart, with months and total of tickets. I need to lines one to represent the date open and the second to represent the date closed. The charts that I have played around with do not seem to effectively map the data in a readable way.

 

[LucyK]

Hi

I was wondering if someone would be able to help me with a workbook chart issue. I am trying to represent open and closed dates on a chart effectively charting the trend of when something opened versus how long it took to close by month. but my excel chart doesn't seem to present that data effectively. Example Picture:

My actual Data is much larger but is restricted to my work laptop I have approx 350 rows of data to try and display in the form of a chart, with months and total of tickets. I need to lines one to represent the date open and the second to represent the date closed. The charts that I have played around with do not seem to effectively map the data in a readable way.
View attachment 67916

As an example of what I am struggling with - the AXIS information isn't right.

 

[Anthony47]

You could calculate in a new column the time elapsed from Created date to Closing date (or up to Today, if not yet closed)
Then you select the Created date column and the Elapsed time and create a scattered graph; this will tell you, with the dates on the X axis, how the closing time evolved in the period
You can add one or more "slicers" to filter for example by Region, or a Timeline to filter by year, month, quarter and other options.
Can you share a sample worksheet (using an external file sharing server), or at least a sample range of data (not a picture, but usable data; see XL2BB - Excel Range to BBCode), and give an idea of which parametres you would like to examine?

 

[LucyK]

You could calculate in a new column the time elapsed from Created date to Closing date (or up to Today, if not yet closed)
Then you select the Created date column and the Elapsed time and create a scattered graph; this will tell you, with the dates on the X axis, how the closing time evolved in the period
You can add one or more "slicers" to filter for example by Region, or a Timeline to filter by year, month, quarter and other options.
Can you share a sample worksheet (using an external file sharing server), or at least a sample range of data (not a picture, but usable data; see XL2BB - Excel Range to BBCode), and give an idea of which parametres you would like

You could calculate in a new column the time elapsed from Created date to Closing date (or up to Today, if not yet closed)
Then you select the Created date column and the Elapsed time and create a scattered graph; this will tell you, with the dates on the X axis, how the closing time evolved in the period
You can add one or more "slicers" to filter for example by Region, or a Timeline to filter by year, month, quarter and other options.
Can you share a sample worksheet (using an external file sharing server), or at least a sample range of data (not a picture, but usable data; see XL2BB - Excel Range to BBCode), and give an idea of which parametres you would like to examine?

Hi Anthony,

Thanks for replying I have attached the example workbook. I need to show a trend between open and closed across 350 rows, and I need it to display Q1 Q2 Q3.

I have included a google drive link to the example worksheet

Any help would be greatly appreciated.

Loading Google Sheets



Thanks

Lucy

 

[Anthony47]

Going on with my suggestion, I inserted in the table the formula to calculate the event duration and also calculate which was the opening Quarter
Then I show in the graph the opening date and the duration.
I added a "linear trendline" (1) to the graph; its formula (2) is shown on the graph, as well its R-square (3; 0.2945 shows a weak correlation to the real data).
I added to the table two "slicers" that can help in selecting the Region and the Quarter to plot (you could have the same effect using the table filter options, but a slicer has a simpler interface; both the slicer are set for multiple choice (4)
The numbers in brackets refer to the highlighted elements on the attached picture

My demo file can be downloaded from my dropbox: 4LK_excel help test.xlsx

I didn't understand what you mean for "I need it to display Q1 Q2 Q3"; in my example you may filter the data by Quarter.