Solved

Count the number of occurances of a ticket grouped by month and then day of the week.

Posted on 2015-01-12
13
76 Views
Last Modified: 2015-01-20
I have a list of ticket numbers in column A and the date and time the ticket was opened in Column b.  I want to create a report that groups the ticket by Month and then counts the number of tickets that were opened on Monday thru Sunday and count them by two time frames between 6 am to 6 pm and 6 pm to 6am.  I have tried a countifs formula unsuccessfully and then I tried a pivot chart and wasn't able to get that work either.  Any ideas?
0
Comment
Question by:Rrave26
  • 5
  • 5
  • 3
13 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40545135
How about a sample for working?
0
 

Author Comment

by:Rrave26
ID: 40545239
I am dealing with column B, incident number, and column AQ, Outage start combined.
Metrics-Tracking-Database.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40546060
In order to be able to summarize the data using a PivotTable and group by months and weeks as you requested, I augmented your original data table by adding two new columns.  One returns the day of the week ("Outage Start Day") and one determines the shift ("Outage Start Time Shift").  The shift returns "Day" or "Night", depending on if the time is between 6AM-6PM (day) or 6:00PM-6:00AM (night).

I then created a PivotTable that groups the Outage Start Date Combined by months, then shows seven day groups, each covering a Monday-Sunday period.  Below each is the shift.  The ticket counts are show in the Values section.

If your needs are different, please detail them and I'll try to help.

Regards,
-Glenn
EE-Metrics-Tracking-Database.xlsx
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Rrave26
ID: 40547235
This is good but I am looking for something slightly different.  Instead of the 7 day groups what I would like to see in the Columns is Sun, Mon, Tue, Wed,Thur, Fri and Sat.  Then the counts underneath each day of the week that are grouped by month and then day or night as the rows.

It would hopefully look like this:

          Sun   Mon  Tue  Wed  Thur  Fri  Sat
Month
 day     4        7
Night    1       1

I hope that is clear.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 40547401
Try this file which uses two different formulas for day and night

=SUM(IF((MONTH($A7)=MONTH(Sheet1!$AQ$2:$AQ$10000))*(YEAR($A7)=YEAR(Sheet1!$AQ$2:$AQ$10000))*(WEEKDAY(Sheet1!$AQ$2:$AQ$10000)=Sheet3!C$5)*(ABS((Sheet1!$AQ$2:$AQ$10000)-INT(Sheet1!$AQ$2:$AQ$10000)-0.5)<0.25),1,0))

=SUM(IF((MONTH($A7)=MONTH(Sheet1!$AQ$2:$AQ$10000))*(YEAR($A7)=YEAR(Sheet1!$AQ$2:$AQ$10000))*(WEEKDAY(Sheet1!$AQ$2:$AQ$10000)=Sheet3!C$5)*(ABS((Sheet1!$AQ$2:$AQ$10000)-INT(Sheet1!$AQ$2:$AQ$10000)-0.5)>0.25),1,0))
Metrics-Tracking-Database.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40547424
Okay, how about this example?

I changed the "Outage Start Day" to return an actual string value for "Mon" through "Sun" (it originally was the same date as "Outage Start Combined" but re-formatted as the day string).  Then I added that field to the Column Labels in the PivotTable.  Finally, I changed the sort option for the column to manual so I could move "Sun" to last column (it sorted Sun-Sat by default).

I finally re-grouped the "Outage Start Date" by Years and Months (noticed that December occurs in 2013 and 2014).


Regards,
-Glenn
EE-Metrics-Tracking-Database.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40547458
The array formula is not complete.  It does not account for all times between 6:00:01 AM and 6:00:00 PM (0.250011574-0.75) as one group and 6:00:01 PM and 6:00:00 AM (0.750011574 - 0.25) completely.  

For example, in December 2013, there are four "day" tickets and two "night" tickets; the array formulas return only 2 and 1, respectively.  See the example file above.

-Glenn
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40547510
In December 2013 I can see only three tickets for Sunday.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40547681
In my example file (which I based on the original), the following rows are December dates that fall on Monday:
2, 9, 10, 11, 27, 32
and their shifts are:
Day, Night, Day, Day, Night, Day

Am I missing something?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40548130
Is that not what I have? 4 + 2 for Monday?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40549105
My apologies, Syed.  Your formulas are absolutely correct.

I was confused since you had Sunday as your first column and the requester was asking for Monday-Sunday as the week range.  Transposing the days fixes this and the formulas hold.

-Glenn
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40549146
Glenn Ray, no problem, again if you look at the desired output posted by the asker later it shows starting Sunday.
0
 

Author Closing Comment

by:Rrave26
ID: 40559675
Thanks for the help.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question