?
Solved

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

Posted on 2015-01-12
13
Medium Priority
?
79 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

752 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