Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting count of unique dates in a list
I have a query in Excel that returns a list of events and what day/time
they occured. I use filter to get to the shift - type - subcategory i want. I end up with a list like below. Defect Type Sub-Category Date Shift Line Glass Type 11 In Press 38555.31221 1 3 2 11 In Press 38555.40115 1 3 2 11 In Press 38567.55948 1 3 2 11 In Press 38569.253 1 3 2 11 In Press 38572.279 1 3 2 11 In Press 38572.29608 1 3 2 11 In Press 38572.30359 1 3 2 11 In Press 38573.46954 1 3 2 11 In Press 38574.44715 1 3 2 11 In Press 38579.38991 1 3 2 11 In Press 38580.59279 1 3 2 11 In Press 38581.47425 1 3 2 11 In Press 38587.29119 1 3 2 The dates are the Excel serial numbers for the dates and times of each event in the query. I want to chart the count of events for each day in the list. So for day 38555 there are 2 events that occured. I would like to have a X-Y chart that has the dates converted into 7/22/05 format and show a Y value of 2 for that count of events on that day. This is driving me crazy on how to work with these date serial numbers and sum them up into something that is chartable. One of my problems is that the solution has to be dynamic so that when I refresh the data query the charting will update accordingly. I would appreciate any help someone can give me on this problem Scott |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting count of unique dates in a list
Make a pivot table. First, format the Date column as dates, to give the PT a
hint. Select the data, choose Pivot Table Report from the Data menu. Drag the Date field to the Row area of the empty PT frame, then drag it again to the Data area. You have a pivot table that looks like this: Count of Date Date Total 7/22/05 7:29 1 7/22/05 9:37 1 8/3/05 13:25 1 8/5/05 6:04 1 8/8/05 6:41 1 8/8/05 7:06 1 8/8/05 7:17 1 8/9/05 11:16 1 8/10/05 10:43 1 8/15/05 9:21 1 8/16/05 14:13 1 8/17/05 11:22 1 8/23/05 6:59 1 Grand Total 13 Right click on any of the dates, and choose Group and Show Detail, then Group. Select Days and unselect Months, change Starting At and Ending At to integral dates (not date-times), and use any date outside of the range, far outside to give yourself room for the data to expand. Your table now looks like this: Count of Date Date Total 22-Jul 2 3-Aug 1 5-Aug 1 8-Aug 3 9-Aug 1 10-Aug 1 15-Aug 1 16-Aug 1 17-Aug 1 23-Aug 1 Grand Total 13 This would make a nice histogram. Make a column chart or a line chart, in either case using a time scale axis. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ wrote in message ups.com... I have a query in Excel that returns a list of events and what day/time they occured. I use filter to get to the shift - type - subcategory i want. I end up with a list like below. Defect Type Sub-Category Date Shift Line Glass Type 11 In Press 38555.31221 1 3 2 11 In Press 38555.40115 1 3 2 11 In Press 38567.55948 1 3 2 11 In Press 38569.253 1 3 2 11 In Press 38572.279 1 3 2 11 In Press 38572.29608 1 3 2 11 In Press 38572.30359 1 3 2 11 In Press 38573.46954 1 3 2 11 In Press 38574.44715 1 3 2 11 In Press 38579.38991 1 3 2 11 In Press 38580.59279 1 3 2 11 In Press 38581.47425 1 3 2 11 In Press 38587.29119 1 3 2 The dates are the Excel serial numbers for the dates and times of each event in the query. I want to chart the count of events for each day in the list. So for day 38555 there are 2 events that occured. I would like to have a X-Y chart that has the dates converted into 7/22/05 format and show a Y value of 2 for that count of events on that day. This is driving me crazy on how to work with these date serial numbers and sum them up into something that is chartable. One of my problems is that the solution has to be dynamic so that when I refresh the data query the charting will update accordingly. I would appreciate any help someone can give me on this problem Scott |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting count of unique dates in a list
On Thu, 1 Jun 2006, in microsoft.public.excel.charting,
Jon Peltier said: Right click on any of the dates, and choose Group and Show Detail, then Group. Select Days and unselect Months, change Starting At and Ending At to integral dates (not date-times), and use any date outside of the range, far outside to give yourself room for the data to expand. Your table now looks like this: Neat. I had never noticed the "Group and Outline" function in Pivot Tables before. Now if only they could do medians, quartiles and percentiles instead of just minimum, maximum, and mean, then I could stop making up my own tables to address the lack. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting count of unique dates in a list
I loose it when I go to group the dates. I made sure the data and the
pivot table date list was formatted to date. I then highlighted all the dates in the pivot table. Right click and grouped them. Here it created one grouop called Group1. From there I do not see how to deselect the months and change the start and end dates like you are talking about. are you talking about charting the pivot table then and then making these changes on the chart? Select Days and unselect Months, change Starting At and Ending At to integral dates (not date-times), and use any date outside of the range, far outside to give yourself room for the data to expand. Here is what my data looks like when it is grouped in the pivot table: Count of Date Date2 Date Total Group1 7/22/05 1 7/22/05 1 8/2/05 1 8/2/05 1 8/3/05 1 8/3/05 1 8/3/05 1 8/4/05 1 8/4/05 1 more data in here......... 5/31/06 1 5/31/06 1 6/1/06 1 6/1/06 1 6/1/06 1 (blank) Grand Total 1085 I also have another question. You say to make a chart and use a time scale axis. How do you do that or do you just mean to put the dates on the X-axis? Thanks so much for your help. Scott Jon Peltier wrote: Make a pivot table. First, format the Date column as dates, to give the PT a hint. Select the data, choose Pivot Table Report from the Data menu. Drag the Date field to the Row area of the empty PT frame, then drag it again to the Data area. You have a pivot table that looks like this: Count of Date Date Total 7/22/05 7:29 1 7/22/05 9:37 1 8/3/05 13:25 1 8/5/05 6:04 1 8/8/05 6:41 1 8/8/05 7:06 1 8/8/05 7:17 1 8/9/05 11:16 1 8/10/05 10:43 1 8/15/05 9:21 1 8/16/05 14:13 1 8/17/05 11:22 1 8/23/05 6:59 1 Grand Total 13 Right click on any of the dates, and choose Group and Show Detail, then Group. Select Days and unselect Months, change Starting At and Ending At to integral dates (not date-times), and use any date outside of the range, far outside to give yourself room for the data to expand. Your table now looks like this: Count of Date Date Total 22-Jul 2 3-Aug 1 5-Aug 1 8-Aug 3 9-Aug 1 10-Aug 1 15-Aug 1 16-Aug 1 17-Aug 1 23-Aug 1 Grand Total 13 This would make a nice histogram. Make a column chart or a line chart, in either case using a time scale axis. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ wrote in message ups.com... I have a query in Excel that returns a list of events and what day/time they occured. I use filter to get to the shift - type - subcategory i want. I end up with a list like below. Defect Type Sub-Category Date Shift Line Glass Type 11 In Press 38555.31221 1 3 2 11 In Press 38555.40115 1 3 2 11 In Press 38567.55948 1 3 2 11 In Press 38569.253 1 3 2 11 In Press 38572.279 1 3 2 11 In Press 38572.29608 1 3 2 11 In Press 38572.30359 1 3 2 11 In Press 38573.46954 1 3 2 11 In Press 38574.44715 1 3 2 11 In Press 38579.38991 1 3 2 11 In Press 38580.59279 1 3 2 11 In Press 38581.47425 1 3 2 11 In Press 38587.29119 1 3 2 The dates are the Excel serial numbers for the dates and times of each event in the query. I want to chart the count of events for each day in the list. So for day 38555 there are 2 events that occured. I would like to have a X-Y chart that has the dates converted into 7/22/05 format and show a Y value of 2 for that count of events on that day. This is driving me crazy on how to work with these date serial numbers and sum them up into something that is chartable. One of my problems is that the solution has to be dynamic so that when I refresh the data query the charting will update accordingly. I would appreciate any help someone can give me on this problem Scott |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting count of unique dates in a list
Almost. When you select a bunch of things and use Group, a group is set up
that includes what you selected. Select a single item, right click, select Group, and proceed per my earlier protocol. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ wrote in message ups.com... I loose it when I go to group the dates. I made sure the data and the pivot table date list was formatted to date. I then highlighted all the dates in the pivot table. Right click and grouped them. Here it created one grouop called Group1. From there I do not see how to deselect the months and change the start and end dates like you are talking about. are you talking about charting the pivot table then and then making these changes on the chart? Select Days and unselect Months, change Starting At and Ending At to integral dates (not date-times), and use any date outside of the range, far outside to give yourself room for the data to expand. Here is what my data looks like when it is grouped in the pivot table: Count of Date Date2 Date Total Group1 7/22/05 1 7/22/05 1 8/2/05 1 8/2/05 1 8/3/05 1 8/3/05 1 8/3/05 1 8/4/05 1 8/4/05 1 more data in here......... 5/31/06 1 5/31/06 1 6/1/06 1 6/1/06 1 6/1/06 1 (blank) Grand Total 1085 I also have another question. You say to make a chart and use a time scale axis. How do you do that or do you just mean to put the dates on the X-axis? Thanks so much for your help. Scott Jon Peltier wrote: Make a pivot table. First, format the Date column as dates, to give the PT a hint. Select the data, choose Pivot Table Report from the Data menu. Drag the Date field to the Row area of the empty PT frame, then drag it again to the Data area. You have a pivot table that looks like this: Count of Date Date Total 7/22/05 7:29 1 7/22/05 9:37 1 8/3/05 13:25 1 8/5/05 6:04 1 8/8/05 6:41 1 8/8/05 7:06 1 8/8/05 7:17 1 8/9/05 11:16 1 8/10/05 10:43 1 8/15/05 9:21 1 8/16/05 14:13 1 8/17/05 11:22 1 8/23/05 6:59 1 Grand Total 13 Right click on any of the dates, and choose Group and Show Detail, then Group. Select Days and unselect Months, change Starting At and Ending At to integral dates (not date-times), and use any date outside of the range, far outside to give yourself room for the data to expand. Your table now looks like this: Count of Date Date Total 22-Jul 2 3-Aug 1 5-Aug 1 8-Aug 3 9-Aug 1 10-Aug 1 15-Aug 1 16-Aug 1 17-Aug 1 23-Aug 1 Grand Total 13 This would make a nice histogram. Make a column chart or a line chart, in either case using a time scale axis. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ wrote in message ups.com... I have a query in Excel that returns a list of events and what day/time they occured. I use filter to get to the shift - type - subcategory i want. I end up with a list like below. Defect Type Sub-Category Date Shift Line Glass Type 11 In Press 38555.31221 1 3 2 11 In Press 38555.40115 1 3 2 11 In Press 38567.55948 1 3 2 11 In Press 38569.253 1 3 2 11 In Press 38572.279 1 3 2 11 In Press 38572.29608 1 3 2 11 In Press 38572.30359 1 3 2 11 In Press 38573.46954 1 3 2 11 In Press 38574.44715 1 3 2 11 In Press 38579.38991 1 3 2 11 In Press 38580.59279 1 3 2 11 In Press 38581.47425 1 3 2 11 In Press 38587.29119 1 3 2 The dates are the Excel serial numbers for the dates and times of each event in the query. I want to chart the count of events for each day in the list. So for day 38555 there are 2 events that occured. I would like to have a X-Y chart that has the dates converted into 7/22/05 format and show a Y value of 2 for that count of events on that day. This is driving me crazy on how to work with these date serial numbers and sum them up into something that is chartable. One of my problems is that the solution has to be dynamic so that when I refresh the data query the charting will update accordingly. I would appreciate any help someone can give me on this problem Scott |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting count of unique dates in a list
I had to redo the pivot table to be able to get the grouping to work.
It was giving me a error that said that it can not group that data. It all works great now though. I had to group it by year,month and day to get it all looking good. It was grouping all the same months together even though they were on different years but the years grouping fixed that. Thanks a ton!!! Scott |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting count of unique dates in a list
I have tried to create another one of these pivot table/charts and I am
running into the same problem. I format the date data from the database query as a date format. Creat the pivot table with date on the row and data window. I go to group the dates and I get the "Cannot group that Selection" error back. Why wont it let me group the selection? I only have 1 of the dates selected. I dont know how I got it to work last time. Thanks Scott |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting count of unique dates in a list
You'll get that error if there are blank cells, or text entries, in the
date column in the source table. wrote: I have tried to create another one of these pivot table/charts and I am running into the same problem. I format the date data from the database query as a date format. Creat the pivot table with date on the row and data window. I go to group the dates and I get the "Cannot group that Selection" error back. Why wont it let me group the selection? I only have 1 of the dates selected. I dont know how I got it to work last time. Thanks Scott -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting count of unique dates in a list
How do you get around that problem if you can only select 1 of the data
points when you do the grouping? Or is there some other way to highlight just the data to group and get it to do the grouping the same way? Thanks Scott |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting count of unique dates in a list
It's the underlying data that affects the ability to group, not the
items that you have currently selected. In the database query, perhaps you could calculate a dummy date where dates are missing. wrote: How do you get around that problem if you can only select 1 of the data points when you do the grouping? Or is there some other way to highlight just the data to group and get it to do the grouping the same way? Thanks Scott -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting count of unique dates in a list
I was able to just delete them from the query results and it worked
perfectly. Thanks for all the help!! Scott |
#12
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting count of unique dates in a list
Great! Thanks for describing how you solved the problem.
wrote: I was able to just delete them from the query results and it worked perfectly. Thanks for all the help!! Scott -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values with Multiple Criteria | Excel Worksheet Functions | |||
Count No. of times Dates are repeated | Excel Discussion (Misc queries) | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
Data Validation using List (But needs unique list in drop down lis | New Users to Excel | |||
count unique with conditions | Excel Worksheet Functions |