Thread: 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

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
_______


<brotherescott@yahoo.com> wrote in message
news:1149201458.116224.145390@f6g2000cwb.googlegroups.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
>

On Thu, 1 Jun 2006, in microsoft.public.excel.charting,
Jon Peltier <jonxlmvpNO@SPAMpeltiertech.com> 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 del@branta.demon.co.uk,
which goes to a spam folder-- please send your email to del3 instead.

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
> _______
>
>
> <brotherescott@yahoo.com> wrote in message
> news:1149201458.116224.145390@f6g2000cwb.googlegroups.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
> >

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
_______


<brotherescott@yahoo.com> wrote in message
news:1149252508.799569.41530@i40g2000cwc.googlegroups.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
>> _______
>>
>>
>> <brotherescott@yahoo.com> wrote in message
>> news:1149201458.116224.145390@f6g2000cwb.googlegroups.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
>> >
>

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

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

You'll get that error if there are blank cells, or text entries, in the
date column in the source table.

brotherescott@yahoo.com 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

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

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.

brotherescott@yahoo.com 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

I was able to just delete them from the query results and it worked
perfectly.
Thanks for all the help!!

Scott

Great! Thanks for describing how you solved the problem.

brotherescott@yahoo.com 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