Counts for Created | Closed | Same Month dates

avicric

Board Regular
Joined
Apr 24, 2017
Messages
59
Office Version
  1. 2019
Platform
  1. Windows
Hi All,
Need your expert help:
I have two data sheets
1) Rawdata for orders recieved
2) Rawdata for escalations
Orders recieved has the created date, closed date.
Escalations has the escalation date, close date
I can get the same month closed count for both sheets....
I am creating relationship between the tables to get the data in one single pivot table but the counts are way off when i try to create the table with created month or closed month....

What I am trying to achieve is a single pivot table which can show by created month total created orders ; total orders closed same month; total closed ; total escalated ; total escalated closed same month ; total escalated closed....
So for example for July-22 :
Total created could be 1000
Same month closed could be 800
Total closed could be 1250 (as previous month pending orders may have been closed)
Total escalated could be 100
Same month escalated closed could be 80
Total escalations closed could be 130

Is this possible...
Thank you
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi All,
Need your expert help:
I have two data sheets
1) Rawdata for orders recieved
2) Rawdata for escalations
Orders recieved has the created date, closed date.
Escalations has the escalation date, close date
I can get the same month closed count for both sheets....
I am creating relationship between the tables to get the data in one single pivot table but the counts are way off when i try to create the table with created month or closed month....

What I am trying to achieve is a single pivot table which can show by created month total created orders ; total orders closed same month; total closed ; total escalated ; total escalated closed same month ; total escalated closed....
So for example for July-22 :
Total created could be 1000
Same month closed could be 800
Total closed could be 1250 (as previous month pending orders may have been closed)
Total escalated could be 100
Same month escalated closed could be 80
Total escalations closed could be 130

Is this possible...
Thank you
No data Sample to view. Use XL2BB to upload Mini Data to understand what you want to do.
 
Upvote 0
No data Sample to view. Use XL2BB to upload Mini Data to understand what you want to do.
Hi below is the Order ID sheet data:-
sample.xlsx
ABCDE
1Oreder IDSr_Created_dateClosed_datederived_statusfinal_state
2220701-00000401-07-2022 00:0301-07-2022 00:05closedRepaired
3220701-00000501-07-2022 00:1001-07-2022 00:13closedRepaired
4220701-00000801-07-2022 00:1401-07-2022 00:16closedRepaired
5220701-00001301-07-2022 00:2401-07-2022 00:25closedRepaired
6220630-00219030-06-2022 22:5201-07-2022 00:27closedRepaired
7220701-00001401-07-2022 00:2601-07-2022 00:27closedRepaired
8220701-00001501-07-2022 00:2801-07-2022 00:31closedRepaired
9220701-00003301-07-2022 00:4501-07-2022 00:49closedRepaired
10220701-00003701-07-2022 00:5001-07-2022 00:52closedRepaired
11220701-00003901-07-2022 00:5301-07-2022 00:57closedRepaired
12220701-00004101-07-2022 00:5701-07-2022 01:00closedRepaired
13220701-00004401-07-2022 01:0101-07-2022 01:03closedRepaired
14220701-00001101-07-2022 00:1801-07-2022 01:07closedRepaired
15220701-00005001-07-2022 01:1901-07-2022 01:21closedRepaired
16220701-00005601-07-2022 01:3201-07-2022 01:36closedRepaired
17220701-00005801-07-2022 01:3701-07-2022 01:38closedRepaired
18220701-00005701-07-2022 01:3601-07-2022 01:41closedRepaired
19220701-00006001-07-2022 01:4601-07-2022 01:49closedRepaired
20220701-00006101-07-2022 01:5001-07-2022 01:53closedRepaired
21220701-00006401-07-2022 02:0301-07-2022 02:05closedRepaired
22220701-00006601-07-2022 02:1101-07-2022 02:13closedRepaired
23220701-00006701-07-2022 02:1401-07-2022 02:16closedRepaired
24220701-00006801-07-2022 02:1701-07-2022 02:20closedRepaired
25220629-00172929-06-2022 16:5501-07-2022 08:11closedRepaired
26220629-00175229-06-2022 16:5901-07-2022 08:12closedRepaired
27220629-00142529-06-2022 15:2501-07-2022 08:13closedRepaired
28220629-00126529-06-2022 14:4601-07-2022 08:18closedRepaired
29220627-00254427-06-2022 22:3801-07-2022 08:55closedRepaired
30220626-00077226-06-2022 13:1101-07-2022 09:02closedRepaired
31220629-00003129-06-2022 04:3601-07-2022 09:28closedRepaired
32220626-00083626-06-2022 13:3201-07-2022 09:31closedRepaired
33220628-00133528-06-2022 14:4001-07-2022 09:31closedRepaired
34220628-00204328-06-2022 18:1001-07-2022 09:32closedRepaired
35220623-00136423-06-2022 16:0701-07-2022 09:36closedRepaired
36220630-00085430-06-2022 12:5401-07-2022 09:42closedRepaired
37220628-00247628-06-2022 22:2901-07-2022 10:01closedRepaired
38220630-00151630-06-2022 16:4201-07-2022 10:03closedRepaired
39220629-00148729-06-2022 15:3801-07-2022 10:09closedRepaired
40220630-00134030-06-2022 15:3901-07-2022 10:11closedRepaired
41220625-00143125-06-2022 15:2401-07-2022 10:13closedRepaired
42220617-00038317-06-2022 09:5201-07-2022 10:16closedRepaired
43220629-00180429-06-2022 17:0501-07-2022 10:19closedRepaired
44220630-00039030-06-2022 10:4201-07-2022 10:21closedRepaired
45220629-00217029-06-2022 18:5601-07-2022 10:22closedRepaired
46220630-00081930-06-2022 12:4201-07-2022 10:24closedRepaired
47220625-00198225-06-2022 18:3801-07-2022 10:26closedRepaired
48220629-00247829-06-2022 22:1501-07-2022 10:27closedRepaired
49220630-00129630-06-2022 15:2301-07-2022 10:29closedRepaired
50220630-00133830-06-2022 15:3901-07-2022 10:29closedRepaired
51220624-00136024-06-2022 16:3301-07-2022 10:36closedRepaired
52220630-00110830-06-2022 14:1401-07-2022 10:40closedRepaired
53220630-00010130-06-2022 08:1401-07-2022 10:42closedRepaired
54220628-00028528-06-2022 10:0401-07-2022 10:43closedRepaired
55220630-00072630-06-2022 12:1401-07-2022 10:48closedRepaired
56220618-00046918-06-2022 11:1801-07-2022 10:51closedRepaired
57220626-00149626-06-2022 18:2801-07-2022 10:57closedRepaired
58220630-00157530-06-2022 17:0301-07-2022 10:58closedRepaired
59220701-00037401-07-2022 10:3701-07-2022 10:58closedRepaired
60220629-00143129-06-2022 15:2501-07-2022 10:59closedRepaired
61220630-00121830-06-2022 14:5701-07-2022 11:03closedRepaired
62220623-00027423-06-2022 10:3501-07-2022 11:05closedRepaired
63220629-00097629-06-2022 13:1301-07-2022 11:06closedRepaired
64220628-00062028-06-2022 11:3701-07-2022 11:07closedRepaired
65220629-00137929-06-2022 15:1501-07-2022 11:07closedRepaired
66220629-00231129-06-2022 19:5701-07-2022 11:07closedRepaired
67220627-00191627-06-2022 17:1001-07-2022 11:09closedRepaired
68220629-00221629-06-2022 19:1301-07-2022 11:09closedRepaired
69220630-00094330-06-2022 13:1801-07-2022 11:09closedRepaired
70220628-00130128-06-2022 14:3101-07-2022 11:10closedRepaired
71220629-00195629-06-2022 17:3601-07-2022 11:10closedRepaired
72220629-00111829-06-2022 13:5201-07-2022 11:11closedRepaired
73220629-00009329-06-2022 08:4101-07-2022 11:12closedRepaired
74220629-00252029-06-2022 23:0101-07-2022 11:12closedRepaired
75220628-00015328-06-2022 09:1701-07-2022 11:13closedRepaired
76220630-00119130-06-2022 14:4701-07-2022 11:14closedRepaired
77220627-00100527-06-2022 12:5101-07-2022 11:18closedRepaired
78220628-00006528-06-2022 07:3201-07-2022 11:18closedRepaired
79220630-00003130-06-2022 03:0001-07-2022 11:18closedRepaired
80220630-00153730-06-2022 16:4501-07-2022 11:18closedRepaired
81220630-00155930-06-2022 16:5601-07-2022 11:18closedRepaired
82220629-00092429-06-2022 13:0101-07-2022 11:19closedRepaired
83220625-00116825-06-2022 13:5701-07-2022 11:20closedRepaired
84220629-00214529-06-2022 18:4601-07-2022 11:20closedRepaired
85220629-00244529-06-2022 21:4001-07-2022 11:20closedRepaired
86220629-00143629-06-2022 15:2701-07-2022 11:23closedRepaired
87220626-00010926-06-2022 09:2401-07-2022 11:24closedRepaired
88220629-00006529-06-2022 08:1101-07-2022 11:25closedRepaired
89220627-00066627-06-2022 11:5301-07-2022 11:26closedRepaired
90220627-00132827-06-2022 14:1901-07-2022 11:26closedRepaired
91220628-00008128-06-2022 08:0301-07-2022 11:27closedRepaired
92220629-00178229-06-2022 17:0101-07-2022 11:28closedRepaired
93220630-00056130-06-2022 11:3301-07-2022 11:28closedRepaired
94220630-00118530-06-2022 14:4501-07-2022 11:28closedRepaired
95220623-00063923-06-2022 12:1501-07-2022 11:30closedRepaired
96220629-00119229-06-2022 14:1701-07-2022 11:31closedRepaired
97220627-00170927-06-2022 16:0001-07-2022 11:32closedRepaired
98220630-00045630-06-2022 11:0201-07-2022 11:32closedRepaired
99220627-00217127-06-2022 18:3401-07-2022 11:34closedRepaired
100220630-00225530-06-2022 23:3801-07-2022 11:34closedRepaired
101220630-00132430-06-2022 15:3401-07-2022 11:35closedRepaired
102220630-00120030-06-2022 14:5201-07-2022 11:37closedRepaired
103220626-00134626-06-2022 17:0201-07-2022 11:38closedRepaired
104220630-00033230-06-2022 10:2401-07-2022 11:38closedRepaired
105220630-00127730-06-2022 15:1801-07-2022 11:38closedRepaired
106220625-00084325-06-2022 12:2701-07-2022 11:40closedRepaired
107220629-00244229-06-2022 21:3401-07-2022 11:43closedRepaired
108220626-00093326-06-2022 14:0301-07-2022 11:48closedRepaired
109220629-00108929-06-2022 13:4501-07-2022 11:50closedRepaired
110220628-00118828-06-2022 13:5601-07-2022 11:51closedRepaired
111220630-00010830-06-2022 08:2101-07-2022 11:51closedRepaired
112220630-00035930-06-2022 10:3201-07-2022 11:51closedRepaired
113220628-00061128-06-2022 11:3501-07-2022 11:52closedRepaired
114220627-00131627-06-2022 14:1801-07-2022 11:55closedRepaired
115220627-00231127-06-2022 19:4001-07-2022 11:55closedRepaired
116220626-00109126-06-2022 15:0301-07-2022 11:56closedRepaired
117220630-00112130-06-2022 14:2101-07-2022 11:58closedRepaired
118220630-00208230-06-2022 21:0201-07-2022 11:58closedRepaired
119220618-00119518-06-2022 14:1901-07-2022 11:59closedRepaired
120220629-00205929-06-2022 18:1401-07-2022 11:59closedRepaired
121220629-00136229-06-2022 15:1101-07-2022 12:00closedRepaired
122220630-00074730-06-2022 12:1901-07-2022 12:00closedRepaired
123220614-00095014-06-2022 12:5001-07-2022 12:01closedRepaired
124220630-00081230-06-2022 12:4001-07-2022 12:01closedRepaired
125220629-00097229-06-2022 13:1101-07-2022 12:03closedRepaired
126220628-00178428-06-2022 17:0101-07-2022 12:05closedRepaired
127220629-00120929-06-2022 14:2201-07-2022 12:05closedRepaired
128220625-00081725-06-2022 12:2201-07-2022 12:06closedRepaired
129220629-00019029-06-2022 09:4301-07-2022 12:06closedRepaired
130220630-00044930-06-2022 10:5901-07-2022 12:06closedRepaired
131220630-00165130-06-2022 17:4101-07-2022 12:06closedRepaired
132220630-00191630-06-2022 19:0901-07-2022 12:06closedRepaired
133220624-00044124-06-2022 10:5701-07-2022 12:07closedRepaired
134220628-00129628-06-2022 14:2901-07-2022 12:08closedRepaired
135220629-00197729-06-2022 17:4201-07-2022 12:08closedRepaired
136220622-00089422-06-2022 13:1701-07-2022 12:09closedRepaired
137220625-00215425-06-2022 20:3201-07-2022 12:09closedRepaired
138220630-00170630-06-2022 17:5601-07-2022 12:09closedRepaired
139220630-00185030-06-2022 18:4601-07-2022 12:09closedRepaired
140220620-00182020-06-2022 17:4501-07-2022 12:12closedRepaired
141220629-00029029-06-2022 10:1101-07-2022 12:12closedRepaired
142220627-00020827-06-2022 09:4301-07-2022 12:14closedRepaired
143220629-00232329-06-2022 20:0201-07-2022 12:14closedRepaired
144220630-00023430-06-2022 09:5301-07-2022 12:14closedRepaired
145220629-00054829-06-2022 11:1901-07-2022 12:16closedRepaired
146220629-00093429-06-2022 13:0201-07-2022 12:16closedRepaired
147220629-00229429-06-2022 19:4901-07-2022 12:17closedRepaired
148220615-00219615-06-2022 18:0101-07-2022 12:18closedRepaired
149220616-00145616-06-2022 14:3101-07-2022 12:18closedRepaired
150220617-00177417-06-2022 16:2701-07-2022 12:18closedRepaired
151220629-00117229-06-2022 14:0901-07-2022 12:18closedRepaired
152220623-00002523-06-2022 03:1701-07-2022 12:19closedRepaired
153220630-00154730-06-2022 16:4901-07-2022 12:20closedRepaired
154220630-00060830-06-2022 11:4901-07-2022 12:23closedRepaired
155220629-00219329-06-2022 19:0501-07-2022 12:24closedRepaired
156220615-00219715-06-2022 18:0101-07-2022 12:25closedRepaired
157220622-00180422-06-2022 19:1201-07-2022 12:25closedRepaired
158220616-00100416-06-2022 12:4101-07-2022 12:26closedRepaired
159220629-00114329-06-2022 13:5801-07-2022 12:26closedRepaired
160220618-00154218-06-2022 16:1701-07-2022 12:27closedRepaired
161220616-00138616-06-2022 14:1401-07-2022 12:28closedRepaired
162220629-00054929-06-2022 11:1901-07-2022 12:28closedRepaired
163220630-00133930-06-2022 15:3901-07-2022 12:28closedRepaired
164220623-00102123-06-2022 14:1801-07-2022 12:29closedRepaired
165220628-00090328-06-2022 12:3501-07-2022 12:29closedRepaired
166220629-00136929-06-2022 15:1201-07-2022 12:29closedRepaired
167220629-00200929-06-2022 17:5301-07-2022 12:29closedRepaired
168220630-00025830-06-2022 10:0101-07-2022 12:29closedRepaired
169220630-00059530-06-2022 11:4401-07-2022 12:29closedRepaired
170220627-00161027-06-2022 15:2701-07-2022 12:30closedRepaired
171220630-00025730-06-2022 10:0101-07-2022 12:30closedRepaired
172220630-00089030-06-2022 13:0301-07-2022 12:30closedRepaired
173220627-00172827-06-2022 16:0501-07-2022 12:31closedRepaired
174220626-00156826-06-2022 19:2101-07-2022 12:32closedRepaired
175220628-00113928-06-2022 13:4001-07-2022 12:32closedRepaired
176220628-00122828-06-2022 14:0901-07-2022 12:32closedRepaired
177220628-00226328-06-2022 19:3401-07-2022 12:32closedRepaired
178220629-00028229-06-2022 10:0901-07-2022 12:32closedRepaired
179220630-00222630-06-2022 23:2101-07-2022 12:32closedRepaired
180220701-00046301-07-2022 11:0101-07-2022 12:32closedRepaired
181220630-00142030-06-2022 16:0601-07-2022 12:33closedRepaired
182220630-00052930-06-2022 11:2401-07-2022 12:34closedRepaired
183220630-00053030-06-2022 11:2401-07-2022 12:34closedRepaired
184220630-00020230-06-2022 09:4301-07-2022 12:35closedRepaired
185220630-00066130-06-2022 12:0101-07-2022 12:35closedRepaired
186220630-00198930-06-2022 19:5101-07-2022 12:35closedRepaired
187220629-00209429-06-2022 18:2801-07-2022 12:36closedRepaired
188220630-00200630-06-2022 20:0001-07-2022 12:36closedRepaired
189220630-00206430-06-2022 20:4501-07-2022 12:36closedRepaired
190220701-00084001-07-2022 12:2801-07-2022 12:36closedRepaired
191220701-00084101-07-2022 12:2801-07-2022 12:36closedRepaired
192220630-00001430-06-2022 00:3501-07-2022 12:37closedRepaired
193220627-00195727-06-2022 17:2201-07-2022 12:38closedRepaired
194220629-00118829-06-2022 14:1501-07-2022 12:39closedRepaired
195220630-00041730-06-2022 10:5101-07-2022 12:39closedRepaired
196220620-00018820-06-2022 10:1001-07-2022 12:40closedRepaired
197220629-00089629-06-2022 12:5901-07-2022 12:40closedRepaired
198220629-00248529-06-2022 22:2501-07-2022 12:41closedRepaired
199220628-00237728-06-2022 20:5201-07-2022 12:43closedRepaired
200220629-00201129-06-2022 17:5401-07-2022 12:44closedRepaired
201220624-00192624-06-2022 22:5601-07-2022 12:45closedRepaired
202220628-00038128-06-2022 10:3301-07-2022 12:46closedRepaired
203220626-00111426-06-2022 15:1301-07-2022 12:47closedRepaired
204220629-00126729-06-2022 14:4601-07-2022 12:47closedRepaired
205220630-00148430-06-2022 16:3001-07-2022 12:47closedRepaired
206220701-00088501-07-2022 12:3901-07-2022 12:47closedRepaired
207220629-00114529-06-2022 13:5801-07-2022 12:48closedRepaired
208220630-00038430-06-2022 10:4001-07-2022 12:48closedRepaired
209220630-00219230-06-2022 22:5301-07-2022 12:48closedRepaired
210220629-00104329-06-2022 13:3601-07-2022 12:49closedRepaired
211220630-00205730-06-2022 20:3901-07-2022 12:50closedRepaired
212220630-00019730-06-2022 09:4101-07-2022 12:51closedRepaired
213220623-00143223-06-2022 16:2901-07-2022 12:53closedRepaired
214220701-00072001-07-2022 12:0101-07-2022 12:53closedRepaired
215220630-00027530-06-2022 10:0701-07-2022 12:54closedRepaired
216220630-00199730-06-2022 19:5801-07-2022 12:54closedRepaired
217220629-00018729-06-2022 09:4301-07-2022 12:56closedRepaired
218220629-00216829-06-2022 18:5501-07-2022 12:56closedRepaired
219220630-00203330-06-2022 20:2001-07-2022 12:56closedRepaired
220220623-00179823-06-2022 18:4001-07-2022 12:58closedRepaired
221220625-00000125-06-2022 00:0201-07-2022 12:58closedRepaired
222220629-00240429-06-2022 20:5701-07-2022 12:58closedRepaired
223220629-00120629-06-2022 14:2201-07-2022 12:59closedRepaired
224220630-00062530-06-2022 11:5401-07-2022 12:59closedRepaired
225220701-00009601-07-2022 07:1701-07-2022 12:59closedRepaired
226220629-00065129-06-2022 11:4401-07-2022 13:00closedRepaired
227220630-00026530-06-2022 10:0501-07-2022 13:00closedRepaired
228220630-00205830-06-2022 20:3901-07-2022 13:00closedRepaired
229220630-00218430-06-2022 22:4801-07-2022 13:00closedRepaired
230220625-00019825-06-2022 09:4001-07-2022 13:01closedRepaired
231220629-00084529-06-2022 12:4601-07-2022 13:01closedRepaired
232220630-00118430-06-2022 14:4401-07-2022 13:01closedRepaired
233220622-00051322-06-2022 11:4301-07-2022 13:02closedRepaired
234220628-00127828-06-2022 14:2401-07-2022 13:03closedRepaired
235220701-00089401-07-2022 12:4101-07-2022 13:03closedRepaired
236220701-00089501-07-2022 12:4101-07-2022 13:03closedRepaired
237220630-00083030-06-2022 12:4601-07-2022 13:04closedRepaired
238220630-00201230-06-2022 20:0301-07-2022 13:04closedRepaired
239220630-00123530-06-2022 15:0201-07-2022 13:06closedRepaired
240220630-00170530-06-2022 17:5601-07-2022 13:06closedRepaired
241220629-00025829-06-2022 10:0201-07-2022 13:07closedRepaired
242220629-00140129-06-2022 15:1801-07-2022 13:08closedRepaired
243220701-00044501-07-2022 10:5801-07-2022 13:08closedRepaired
244220630-00019030-06-2022 09:3801-07-2022 13:10closedRepaired
245220627-00092927-06-2022 12:3301-07-2022 13:11closedRepaired
246220621-00137621-06-2022 16:1201-07-2022 14:56closedRepaired
247220613-00140113-06-2022 14:5101-07-2022 15:59closedRepaired
248220606-00048406-06-2022 11:2201-07-2022 16:40closedRepaired
249220619-00088419-06-2022 15:0901-07-2022 17:49closedRepaired
250220630-00099030-06-2022 13:3201-07-2022 18:09closedRepaired
251220531-00115031-05-2022 14:4101-07-2022 18:58closedRepaired
252220626-00140626-06-2022 17:3602-07-2022 14:17closedRepaired
253220611-00206811-06-2022 21:0202-07-2022 16:52closedRepaired
254220613-00116613-06-2022 13:4602-07-2022 19:31closedRepaired
255220623-00063123-06-2022 12:1302-07-2022 20:52closedRepaired
256220701-00185401-07-2022 18:1503-07-2022 10:30closedRepaired
257220621-00157021-06-2022 17:3003-07-2022 13:04closedRepaired
258220621-00120021-06-2022 14:5703-07-2022 17:39closedRepaired
259220628-00089728-06-2022 12:3403-07-2022 18:02closedRepaired
260220630-00105130-06-2022 13:5003-07-2022 18:05closedRepaired
261220602-00160002-06-2022 17:3703-07-2022 18:11closedRepaired
262220624-00036324-06-2022 10:3003-07-2022 18:14closedRepaired
263220619-00129119-06-2022 19:0103-07-2022 18:18closedRepaired
264220629-00231929-06-2022 19:5904-07-2022 09:54closedRepaired
265220629-00154729-06-2022 15:5904-07-2022 11:11closedRepaired
266220524-00048824-05-2022 11:5004-07-2022 12:28closedRepaired
Orders recived


Below is the escalation sheet data:-
sample.xlsx
ABCDEF
1Oreder IDStatusfinal_stateDate_CreatedDate_ClosedEscalation date
2220524-000488ClosedEscalated24-05-202204-07-202201-07-2022
3220531-001150ClosedEscalated31-05-202201-07-202229-06-2022
4220602-001600ClosedEscalated02-06-202203-07-202201-07-2022
5220606-000484ClosedEscalated06-06-202201-07-202230-06-2022
6220611-002068ClosedEscalated11-06-202202-07-202202-07-2022
7220613-001166ClosedEscalated13-06-202202-07-202230-06-2022
8220613-001401ClosedEscalated13-06-202201-07-202201-07-2022
9220619-000884ClosedEscalated19-06-202201-07-202228-06-2022
10220619-001291ClosedEscalated19-06-202203-07-202202-07-2022
11220620-000188ClosedEscalated20-06-202201-07-202230-06-2022
12220621-001200ClosedEscalated21-06-202203-07-202223-06-2022
13220621-001376ClosedEscalated21-06-202201-07-202230-06-2022
14220621-001570ClosedEscalated21-06-202203-07-202229-06-2022
15220623-000631ClosedEscalated23-06-202202-07-202225-06-2022
16220624-000363ClosedEscalated24-06-202203-07-202228-06-2022
17220624-001360ClosedEscalated24-06-202201-07-202230-06-2022
18220626-001406ClosedEscalated26-06-202202-07-202230-06-2022
19220628-000897ClosedEscalated28-06-202203-07-202202-07-2022
20220629-001436ClosedEscalated29-06-202201-07-202230-06-2022
21220629-001547ClosedEscalated29-06-202204-07-202202-07-2022
22220629-002319ClosedEscalated29-06-202204-07-202202-07-2022
23220630-000990ClosedEscalated30-06-202201-07-202230-06-2022
24220630-001051ClosedEscalated30-06-202203-07-202202-07-2022
25220701-000037ClosedEscalated01-07-202201-07-202230-06-2022
26220701-000039ClosedEscalated01-07-202201-07-202230-06-2022
27220701-000041ClosedEscalated01-07-202201-07-202230-06-2022
28220701-000044ClosedEscalated01-07-202201-07-202230-06-2022
29220701-000058ClosedEscalated01-07-202201-07-202230-06-2022
30220701-001854ClosedEscalated01-07-202203-07-202202-07-2022
Escalations
 
Upvote 0
I started with you sample data. Formatted it but when it came to creating Pivot Table with Multiple Tables, did I realize that Excel for Mac (that I'm using) does not support that.
 
Upvote 0
What I am trying to achieve is a single pivot table which can show by created month total created orders ; total orders closed same month; total closed ; total escalated ; total escalated closed same month ; total escalated closed....
Pivot tables I told you, I couldn't work. See if underneath works for you. I could only copy limited cells that XL2BB allows but Summary is purely based on data you provided. Hope it helps you in some way.

Book1
ABCDEFGHIJKLMNOPQRSTUVWX
1SummaryOreder IDSr_Created_dateClosed_datederived_statusfinal_stateCr_MonthCl_MonthOreder IDStatusfinal_stateDate_CreatedDate_ClosedEscalation dateCr_MonthCl_MonthEsc_Month
2MonthOrder CreatedOrder ClosedCreatedClosedEscalations220701-00000444743.0020844743.00347closedRepairedJulJul220524-000488ClosedEscalated447054474644743.45321MayJulJul
3May20200220701-00000544743.0069444743.00903closedRepairedJulJul220531-001150ClosedEscalated447124474344741.16749MayJulJun
4Jun230021019220701-00000844743.0097244743.01111closedRepairedJulJul220602-001600ClosedEscalated447144474544743.4596JunJulJul
5Jul3326562910220701-00001344743.0166744743.01736closedRepairedJulJul220606-000484ClosedEscalated447184474344742.37251JunJulJun
6220630-00219044742.9527844743.01875closedRepairedJunJul220611-002068ClosedEscalated447234474444744.33758JunJulJul
7220701-00001444743.0180644743.01875closedRepairedJulJul220613-001166ClosedEscalated447254474444742.59313JunJulJun
8220701-00001544743.0194444743.02153closedRepairedJulJul220613-001401ClosedEscalated447254474344743.17035JunJulJul
9220701-00003344743.0312544743.03403closedRepairedJulJul220619-000884ClosedEscalated447314474344740.23663JunJulJun
10220701-00003744743.0347244743.03611closedRepairedJulJul220619-001291ClosedEscalated447314474544744.17633JunJulJul
11220701-00003944743.0368144743.03958closedRepairedJulJul220620-000188ClosedEscalated447324474344742.451JunJulJun
12220701-00004144743.0395844743.04167closedRepairedJulJul220621-001200ClosedEscalated447334474544735.38824JunJulJun
13220701-00004444743.0423644743.04375closedRepairedJulJul220621-001376ClosedEscalated447334474344742.4442JunJulJun
14220701-00001144743.012544743.04653closedRepairedJulJul220621-001570ClosedEscalated447334474544741.58759JunJulJun
15220701-00005044743.0548644743.05625closedRepairedJulJul220623-000631ClosedEscalated447354474444737.43688JunJulJun
16220701-00005644743.0638944743.06667closedRepairedJulJul220624-000363ClosedEscalated447364474544740.33628JunJulJun
17220701-00005844743.0673644743.06806closedRepairedJulJul220624-001360ClosedEscalated447364474344742.58303JunJulJun
18220701-00005744743.0666744743.07014closedRepairedJulJul220626-001406ClosedEscalated447384474444742.35006JunJulJun
19220701-00006044743.0736144743.07569closedRepairedJulJul220628-000897ClosedEscalated447404474544744.17526JunJulJul
20220701-00006144743.0763944743.07847closedRepairedJulJul220629-001436ClosedEscalated447414474344742.52167JunJulJun
21220701-00006444743.0854244743.08681closedRepairedJulJul220629-001547ClosedEscalated447414474644744.17226JunJulJul
22220701-00006644743.0909744743.09236closedRepairedJulJul220629-002319ClosedEscalated447414474644744.58295JunJulJul
23220701-00006744743.0930644743.09444closedRepairedJulJul220630-000990ClosedEscalated447424474344742.52775JunJulJun
24220701-00006844743.0951444743.09722closedRepairedJulJul220630-001051ClosedEscalated447424474544744.1794JunJulJul
25220629-00172944741.7048644743.34097closedRepairedJunJul220701-000037ClosedEscalated447434474344742.80635JulJulJun
26220629-00175244741.7076444743.34167closedRepairedJunJul220701-000039ClosedEscalated447434474344742.8091JulJulJun
27220629-00142544741.6423644743.34236closedRepairedJunJul220701-000041ClosedEscalated447434474344742.81184JulJulJun
28220629-00126544741.6152844743.34583closedRepairedJunJul220701-000044ClosedEscalated447434474344742.81428JulJulJun
29220627-00254444739.9430644743.37153closedRepairedJunJul220701-000058ClosedEscalated447434474344742.83871JulJulJun
30220626-00077244738.5493144743.37639closedRepairedJunJul220701-001854ClosedEscalated447434474544744.46122JulJulJul
Sheet1
Cell Formulas
RangeFormula
M2:M30M2=TEXT([@[Sr_Created_date]],"mmm")
N2:N30N2=TEXT([@[Closed_date]],"mmm")
V2:V30V2=TEXT([@[Date_Created]],"mmm")
W2:W30W2=TEXT([@[Date_Closed]],"mmm")
X2:X30X2=TEXT([@[Escalation date]],"mmm")
B3:B5B3=COUNTIFS(Table1[Cr_Month],[@Month])
C3:C5C3=COUNTIFS(Table1[Cl_Month],[@Month])
D3:D5D3=COUNTIFS(Table2[Cr_Month],[@Month])
E3:E5E3=COUNTIFS(Table2[Cl_Month],[@Month])
F3:F5F3=COUNTIFS(Table2[Esc_Month],[@Month])
 
Upvote 0
Pivot tables I told you, I couldn't work. See if underneath works for you. I could only copy limited cells that XL2BB allows but Summary is purely based on data you provided. Hope it helps you in some way.

Book1
ABCDEFGHIJKLMNOPQRSTUVWX
1SummaryOreder IDSr_Created_dateClosed_datederived_statusfinal_stateCr_MonthCl_MonthOreder IDStatusfinal_stateDate_CreatedDate_ClosedEscalation dateCr_MonthCl_MonthEsc_Month
2MonthOrder CreatedOrder ClosedCreatedClosedEscalations220701-00000444743.0020844743.00347closedRepairedJulJul220524-000488ClosedEscalated447054474644743.45321MayJulJul
3May20200220701-00000544743.0069444743.00903closedRepairedJulJul220531-001150ClosedEscalated447124474344741.16749MayJulJun
4Jun230021019220701-00000844743.0097244743.01111closedRepairedJulJul220602-001600ClosedEscalated447144474544743.4596JunJulJul
5Jul3326562910220701-00001344743.0166744743.01736closedRepairedJulJul220606-000484ClosedEscalated447184474344742.37251JunJulJun
6220630-00219044742.9527844743.01875closedRepairedJunJul220611-002068ClosedEscalated447234474444744.33758JunJulJul
7220701-00001444743.0180644743.01875closedRepairedJulJul220613-001166ClosedEscalated447254474444742.59313JunJulJun
8220701-00001544743.0194444743.02153closedRepairedJulJul220613-001401ClosedEscalated447254474344743.17035JunJulJul
9220701-00003344743.0312544743.03403closedRepairedJulJul220619-000884ClosedEscalated447314474344740.23663JunJulJun
10220701-00003744743.0347244743.03611closedRepairedJulJul220619-001291ClosedEscalated447314474544744.17633JunJulJul
11220701-00003944743.0368144743.03958closedRepairedJulJul220620-000188ClosedEscalated447324474344742.451JunJulJun
12220701-00004144743.0395844743.04167closedRepairedJulJul220621-001200ClosedEscalated447334474544735.38824JunJulJun
13220701-00004444743.0423644743.04375closedRepairedJulJul220621-001376ClosedEscalated447334474344742.4442JunJulJun
14220701-00001144743.012544743.04653closedRepairedJulJul220621-001570ClosedEscalated447334474544741.58759JunJulJun
15220701-00005044743.0548644743.05625closedRepairedJulJul220623-000631ClosedEscalated447354474444737.43688JunJulJun
16220701-00005644743.0638944743.06667closedRepairedJulJul220624-000363ClosedEscalated447364474544740.33628JunJulJun
17220701-00005844743.0673644743.06806closedRepairedJulJul220624-001360ClosedEscalated447364474344742.58303JunJulJun
18220701-00005744743.0666744743.07014closedRepairedJulJul220626-001406ClosedEscalated447384474444742.35006JunJulJun
19220701-00006044743.0736144743.07569closedRepairedJulJul220628-000897ClosedEscalated447404474544744.17526JunJulJul
20220701-00006144743.0763944743.07847closedRepairedJulJul220629-001436ClosedEscalated447414474344742.52167JunJulJun
21220701-00006444743.0854244743.08681closedRepairedJulJul220629-001547ClosedEscalated447414474644744.17226JunJulJul
22220701-00006644743.0909744743.09236closedRepairedJulJul220629-002319ClosedEscalated447414474644744.58295JunJulJul
23220701-00006744743.0930644743.09444closedRepairedJulJul220630-000990ClosedEscalated447424474344742.52775JunJulJun
24220701-00006844743.0951444743.09722closedRepairedJulJul220630-001051ClosedEscalated447424474544744.1794JunJulJul
25220629-00172944741.7048644743.34097closedRepairedJunJul220701-000037ClosedEscalated447434474344742.80635JulJulJun
26220629-00175244741.7076444743.34167closedRepairedJunJul220701-000039ClosedEscalated447434474344742.8091JulJulJun
27220629-00142544741.6423644743.34236closedRepairedJunJul220701-000041ClosedEscalated447434474344742.81184JulJulJun
28220629-00126544741.6152844743.34583closedRepairedJunJul220701-000044ClosedEscalated447434474344742.81428JulJulJun
29220627-00254444739.9430644743.37153closedRepairedJunJul220701-000058ClosedEscalated447434474344742.83871JulJulJun
30220626-00077244738.5493144743.37639closedRepairedJunJul220701-001854ClosedEscalated447434474544744.46122JulJulJul
Sheet1
Cell Formulas
RangeFormula
M2:M30M2=TEXT([@[Sr_Created_date]],"mmm")
N2:N30N2=TEXT([@[Closed_date]],"mmm")
V2:V30V2=TEXT([@[Date_Created]],"mmm")
W2:W30W2=TEXT([@[Date_Closed]],"mmm")
X2:X30X2=TEXT([@[Escalation date]],"mmm")
B3:B5B3=COUNTIFS(Table1[Cr_Month],[@Month])
C3:C5C3=COUNTIFS(Table1[Cl_Month],[@Month])
D3:D5D3=COUNTIFS(Table2[Cr_Month],[@Month])
E3:E5E3=COUNTIFS(Table2[Cl_Month],[@Month])
F3:F5F3=COUNTIFS(Table2[Esc_Month],[@Month])
Thank you kindly and sorry for the delayed response...

I got a solution....

Just for the needy...to achieve my goal....

Had to create relationship between
Had to create a calendar
1) Same month and date column (deactivate it)
2) Created month and date column (deactivate it)
3) Closed month and date column (activate it)
Then use the userrelationship function to get my desired results....
Phewww
 
Upvote 0
Solution

Forum statistics

Threads
1,223,977
Messages
6,175,753
Members
452,667
Latest member
vanessavalentino83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top