Week over Week Change for tickets that are Open, Resolved, and Pending

[bgf2018]

Hello I need help setting up a weekly report table or pivot that shows the number of open, resolved, pending, and new added tickets. The new data for the report comes out every Monday morning and I will send my report every Monday afternoon showing the number of open, resolved, pending, and new added tickets. The goal is to show the week over week change with the tickets. Some tickets are resolved when the new data comes out and also new tickets are added to the data every Monday. The problem is to my boss it seem like none of the tickets are being resolved with the new added tickets in the data. The report is needed to show the break down

There is about 15k- 20k records a week in the data, and about 100-300 added weekly. The new report will have all of the previous week tickets(open, pending resolved and resolved) plus the new added tickets.



I need the report to look like this or in a PIVOT

Here is the data. Report 4-5-21 and Report 4-12-21



Report 4-5-21

wow 2.xlsx
	A	B	C	D	E	F
1	Loan ID	Loan Number	Doc Type	Error Code	Doc Status	Resolved Date
2	1856445220VANNRN44	1856445220	VANN	RN44	Resolved	4/4/2021
3	1856445223TRUCMTCC	1856445223	TRUC	MTCC	Resolved	4/4/2021
4	1702334522TRUCPEND	1702334522	TRUC	PEND	Resolved	4/3/2021
5	8152038986BIKEARDM	8152038986	BIKE	ARDM	Resolved	4/3/2021
6	8152038987TRUCIFIX	8152038987	TRUC	IFIX	Resolved	4/3/2021
7	1856445218TRUCIFIX	1856445218	TRUC	IFIX	Pending
8	1856445219VANNPEND	1856445219	VANN	PEND	Pending
9	1856445221BIKERN21	1856445221	BIKE	RN21	Pending
10	1856445224WALKPEND	1856445224	WALK	PEND	Pending
11	1856445225BUSSPEND	1856445225	BUSS	PEND	Pending
12	1702334519TRUCRN21	1702334519	TRUC	RN21	Pending
13	1702334521RIDEIFIX	1702334521	RIDE	IFIX	Pending
14	1727453227BIKEPEND	1727453227	BIKE	PEND	Pending
15	1727453229VANNARDM	1727453229	VANN	ARDM	Pending
16	1856445217CARRRIIC	1856445217	CARR	RIIC	Open
17	1856445222CARRPEND	1856445222	CARR	PEND	Open
18	1702334517CARRARDM	1702334517	CARR	ARDM	Open
19	1702334518CARRIFIX	1702334518	CARR	IFIX	Open
20	1702334520BIKEARDM	1702334520	BIKE	ARDM	Open
21	8152038985WALKRN23	8152038985	WALK	RN23	Open
22	8152038988BUSSARDM	8152038988	BUSS	ARDM	Open
23	8152038989TRUCMTCC	8152038989	TRUC	MTCC	Open
24	1727453226WALKIFIX	1727453226	WALK	IFIX	Open
25	1727453227TRUCMTCC	1727453227	TRUC	MTCC	Open
26	1727453228TRUCPEND	1727453228	TRUC	PEND	Open
27	1727453228WALKPEND	1727453228	WALK	PEND	Open
4-5-21 tickets

Cell Formulas
Range	Formula
A2:A27	A2	=CONCATENATE(B2,C2,D2,)

next week Report 4-12-21

wow 2.xlsx
	A	B	C	D	E	F	G
1	Loan ID	Loan Number	Doc Type	Error Code	Doc Status	Resolved Date	NEW ADDED LOANS
2	1856445220VANNRN44	1856445220	VANN	RN44	Resolved	4/4/2021	OLD
3	1856445223TRUCMTCC	1856445223	TRUC	MTCC	Resolved	4/4/2021	OLD
4	1702334522TRUCPEND	1702334522	TRUC	PEND	Resolved	4/3/2021	OLD
5	8152038986BIKEARDM	8152038986	BIKE	ARDM	Resolved	4/3/2021	OLD
6	8152038987TRUCIFIX	8152038987	TRUC	IFIX	Resolved	4/3/2021	OLD
7	2217896553TRUCPEND	2217896553	TRUC	PEND	Resolved	4/10/2021	OLD
8	2217896554WALKRN23	2217896554	WALK	RN23	Resolved	4/10/2021	OLD
9	2217896555BIKEARDM	2217896555	BIKE	ARDM	Resolved	4/11/2021	NEW
10	1856445218TRUCIFIX	1856445218	TRUC	IFIX	Pending		OLD
11	1856445219VANNPEND	1856445219	VANN	PEND	Pending		OLD
12	1856445221BIKERN21	1856445221	BIKE	RN21	Pending		OLD
13	1856445224WALKPEND	1856445224	WALK	PEND	Pending		OLD
14	1856445225BUSSPEND	1856445225	BUSS	PEND	Pending		OLD
15	1702334519TRUCRN21	1702334519	TRUC	RN21	Pending		OLD
16	1702334521RIDEIFIX	1702334521	RIDE	IFIX	Pending		OLD
17	1727453227BIKEPEND	1727453227	BIKE	PEND	Pending		OLD
18	1727453229VANNARDM	1727453229	VANN	ARDM	Pending		OLD
19	1856445217CARRRIIC	1856445217	CARR	RIIC	Open		OLD
20	1856445222CARRPEND	1856445222	CARR	PEND	Open		OLD
21	1702334517CARRARDM	1702334517	CARR	ARDM	Open		OLD
22	1702334518CARRIFIX	1702334518	CARR	IFIX	Open		OLD
23	1702334520BIKEARDM	1702334520	BIKE	ARDM	Open		OLD
24	8152038985WALKRN23	8152038985	WALK	RN23	Open		OLD
25	8152038988BUSSARDM	8152038988	BUSS	ARDM	Open		OLD
26	8152038989TRUCMTCC	8152038989	TRUC	MTCC	Open		OLD
27	1727453226WALKIFIX	1727453226	WALK	IFIX	Open		OLD
28	1727453227TRUCMTCC	1727453227	TRUC	MTCC	Open		OLD
29	1727453228TRUCPEND	1727453228	TRUC	PEND	Open		OLD
30	1727453228WALKPEND	1727453228	WALK	PEND	Open		OLD
31	8812563277TRUCMTCC	8812563277	TRUC	MTCC	Open		NEW
32	8812563278WALKIFIX	8812563278	WALK	IFIX	Open		NEW
33	8812563279BIKEPEND	8812563279	BIKE	PEND	Open		NEW
34	8812563280TRUCMTCC	8812563280	TRUC	MTCC	Open		NEW
35	8812563281TRUCPEND	8812563281	TRUC	PEND	Open		NEW
36	8812563281BIKEPEND	8812563281	BIKE	PEND	Open		NEW
37	8812563281VANNARDM	8812563281	VANN	ARDM	Open		NEW
38	2217896552RIDEIFIX	2217896552	RIDE	IFIX	Open		NEW
39	3389754221TRUCIFIX	3389754221	TRUC	IFIX	Open		NEW
40	3389754222BUSSARDM	3389754222	BUSS	ARDM	Open		NEW
41	3389754223TRUCMTCC	3389754223	TRUC	MTCC	Open		NEW
4-12-21 tickets

Cell Formulas
Range	Formula
A2:A41	A2	=CONCATENATE(B2,C2,D2,)

 

[bgf2018]

Need the report to be in a table or pivot

wow 2.xlsx
	A	B	C	D	E
1
2	Working Status	4/5/2021	4/12/2021	4/19/2021	Total
3	Pending Resolved	9	9		-9
4	Resolved	5	8		-8
5	Newly Added	0	14		-14
6	Open	12	23		-23
7	Grand Total	26	54		-54
TWL

Cell Formulas
Range	Formula
E7,B7:C7	B7	=SUM(B3:B6)
E3:E6	E3	=SUM(D3-C3)