Help with simple pivot table

[mr_m_cox]

Hi All,

I have two columns of dates. One is support tickets opened one is support tickets closed. Each date entry on a new row represents a ticket.

When I try to put a pivot chart together the opened and closed per month figure is coming out the same which it shouldn't be.

What I would like to end up with is a combo chart showing bars for each month of tickets opened and a line on top of that showing closed per month.

Any advice would be super useful.

Thanks,

Matt

 

[cyrilbrd]

Hi,
Given a set of data as follows:

Book1
	B	C	D
1	ticket	date 1	date 2
2	1	15-Nov-17	21-Nov-17
3	2	16-Nov-17	25-Nov-17
4	3	20-Nov-17	29-Nov-17
5	4	24-Nov-17	04-Dec-17
6	5	29-Nov-17	06-Dec-17
7	6	29-Nov-17	06-Dec-17
8	7	02-Dec-17	07-Dec-17
9	8	06-Dec-17	10-Dec-17
10	9	08-Dec-17	10-Dec-17
11	10	08-Dec-17	10-Dec-17
12	11	11-Dec-17	15-Dec-17
13	12	12-Dec-17	20-Dec-17
14	13	17-Dec-17	21-Dec-17
15	14	19-Dec-17	22-Dec-17
16	15	23-Dec-17	22-Dec-17
17	16	25-Dec-17	27-Dec-17
18	17	27-Dec-17	31-Dec-17
19	18	30-Dec-17	03-Jan-18
20	19	04-Jan-18	06-Jan-18
21	20	07-Jan-18	11-Jan-18
22	21	11-Jan-18	11-Jan-18
23	22	14-Jan-18	16-Jan-18
24	23	17-Jan-18	18-Jan-18
25	24	19-Jan-18	19-Jan-18
26	25	21-Jan-18	24-Jan-18
27	26	21-Jan-18	29-Jan-18
28	27	24-Jan-18	29-Jan-18
29	28	25-Jan-18	29-Jan-18
30	29	30-Jan-18	31-Jan-18
31	30	03-Feb-18	05-Feb-18
32	31	05-Feb-18	07-Feb-18
mr_m_cox

And a pivot as follows:

Book1
	G	H	I
2	Row Labels	Average of date 1	Average of date 2
3	1	15-Nov-17	21-Nov-17
4	2	16-Nov-17	25-Nov-17
5	3	20-Nov-17	29-Nov-17
6	4	24-Nov-17	04-Dec-17
7	5	29-Nov-17	06-Dec-17
8	6	29-Nov-17	06-Dec-17
9	7	02-Dec-17	07-Dec-17
10	8	06-Dec-17	10-Dec-17
11	9	08-Dec-17	10-Dec-17
12	10	08-Dec-17	10-Dec-17
13	11	11-Dec-17	15-Dec-17
14	12	12-Dec-17	20-Dec-17
15	13	17-Dec-17	21-Dec-17
16	14	19-Dec-17	22-Dec-17
17	15	23-Dec-17	22-Dec-17
18	16	25-Dec-17	27-Dec-17
19	17	27-Dec-17	31-Dec-17
20	18	30-Dec-17	03-Jan-18
21	19	04-Jan-18	06-Jan-18
22	20	07-Jan-18	11-Jan-18
23	21	11-Jan-18	11-Jan-18
24	22	14-Jan-18	16-Jan-18
25	23	17-Jan-18	18-Jan-18
26	24	19-Jan-18	19-Jan-18
27	25	21-Jan-18	24-Jan-18
28	26	21-Jan-18	29-Jan-18
29	27	24-Jan-18	29-Jan-18
30	28	25-Jan-18	29-Jan-18
31	29	30-Jan-18	31-Jan-18
32	30	03-Feb-18	05-Feb-18
33	31	05-Feb-18	07-Feb-18
mr_m_cox

Click on the PT, select recommended chart, select Combo, select Clustered column - Line. With date 1 as clustered Column and date 2 as Line.
Is this what you required?