Solved

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

Posted on 2015-01-12
13
65 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
Comment Utility
How about a sample for working?
0
 

Author Comment

by:Rrave26
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Rrave26
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
Comment Utility
In December 2013 I can see only three tickets for Sunday.
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
Comment Utility
Is that not what I have? 4 + 2 for Monday?
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for the help.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now