Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old July 29th 08, 05:20 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 5
Default Adding multiple series to charts

I want to track the widget buying history of each customer. I already have
sales data on how many widgets have been purchased by a distinct customer on
a specific date. There are other helpful fields such as geographic region,
customer size, etc. I've already been able to massage the data a bit and use
pivot tables so I can get statistics such as total widgets per region per
quarter, widgets per quarter purchased by new vs. existing customers, etc.

I'm trying to prove or disprove a hypothesis about the data; namely, are
most widget purchases made simultaneously with the initial sale of the first
widget or afterward? I was hoping that by graphing the widget buying history
of a number of customers, this would become more apparent. This is why I
planned to make each customer into its own series so I could graph out the
customer's buying history over time.

I plan on segmenting the customers by geographic region and customer size.
If I still have more than 255 customers per segment then I was simply going
to make each segment smaller by tweaking the customer size categories.

I'm doing this analysis mostly for myself so I can draw some conclusions
about our customers and their buying patterns, so you are correct in saying
that legibility isn't the foremost concern.

Hopefully my questions make more sense! I'm still fairly new to Excel
charting but I'm doing my best to learn as much as I can to get the job done.

"Jon Peltier" wrote:

It depends on what you want to show. As I asked...

Does each customer need its own series? Are you interested in
widgets per day, cumulative widgets, or something else?


Another thing you need is legibility, but your posts don't indicate that
you're too concerned with that.

What you need to think about includes:

* do I understand my client?
- do I understand the data?
- do I understand what they need to learn from the chart?
- do I know how they currently display the data?
* what are my plotted values?
* how do I segment the data?
- what are the categories? dates? parts? customer? something else?
- how do I define my series? customer? part number? classifications of
parts?

- Jon



  #12   Report Post  
Old July 29th 08, 11:17 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Adding multiple series to charts

How are you going to differentiate from among 255 customers on a chart?
What type of chart is it?

I wonder if using a set of sparklines might be what you need. These people
have a good product:

http://www.bonavistasystems.com/Prod..._Overview.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"rparker" wrote in message
...
I want to track the widget buying history of each customer. I already have
sales data on how many widgets have been purchased by a distinct customer
on
a specific date. There are other helpful fields such as geographic
region,
customer size, etc. I've already been able to massage the data a bit and
use
pivot tables so I can get statistics such as total widgets per region per
quarter, widgets per quarter purchased by new vs. existing customers, etc.

I'm trying to prove or disprove a hypothesis about the data; namely, are
most widget purchases made simultaneously with the initial sale of the
first
widget or afterward? I was hoping that by graphing the widget buying
history
of a number of customers, this would become more apparent. This is why I
planned to make each customer into its own series so I could graph out the
customer's buying history over time.

I plan on segmenting the customers by geographic region and customer size.
If I still have more than 255 customers per segment then I was simply
going
to make each segment smaller by tweaking the customer size categories.

I'm doing this analysis mostly for myself so I can draw some conclusions
about our customers and their buying patterns, so you are correct in
saying
that legibility isn't the foremost concern.

Hopefully my questions make more sense! I'm still fairly new to Excel
charting but I'm doing my best to learn as much as I can to get the job
done.

"Jon Peltier" wrote:

It depends on what you want to show. As I asked...

Does each customer need its own series? Are you interested in
widgets per day, cumulative widgets, or something else?


Another thing you need is legibility, but your posts don't indicate that
you're too concerned with that.

What you need to think about includes:

* do I understand my client?
- do I understand the data?
- do I understand what they need to learn from the chart?
- do I know how they currently display the data?
* what are my plotted values?
* how do I segment the data?
- what are the categories? dates? parts? customer? something else?
- how do I define my series? customer? part number? classifications of
parts?

- Jon




  #13   Report Post  
Old July 30th 08, 04:22 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 5
Default Adding multiple series to charts

I was thinking about using an xy scatter plot because I wanted to plot time
on the x axis and number of widgets purchased on the y axis. So the x axis
is definitely treated as a value rather than a category.

I actually don't care too much about differentiating among the 255
customers; I'm mainly looking for general trends in buying behavior. I was
thinking that if I graph a large number of customers and juxtapose their
buying behavior together, certain patterns will emerge.

Sparklines is an interesting concept. The biggest issue I see with them is
that they don't allow me to map many simultaneously together, which is what
I'm primarily interested in. The aggregate is more important than the
individual customer.

"Jon Peltier" wrote:

How are you going to differentiate from among 255 customers on a chart?
What type of chart is it?

I wonder if using a set of sparklines might be what you need. These people
have a good product:

http://www.bonavistasystems.com/Prod..._Overview.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"rparker" wrote in message
...
I want to track the widget buying history of each customer. I already have
sales data on how many widgets have been purchased by a distinct customer
on
a specific date. There are other helpful fields such as geographic
region,
customer size, etc. I've already been able to massage the data a bit and
use
pivot tables so I can get statistics such as total widgets per region per
quarter, widgets per quarter purchased by new vs. existing customers, etc.

I'm trying to prove or disprove a hypothesis about the data; namely, are
most widget purchases made simultaneously with the initial sale of the
first
widget or afterward? I was hoping that by graphing the widget buying
history
of a number of customers, this would become more apparent. This is why I
planned to make each customer into its own series so I could graph out the
customer's buying history over time.

I plan on segmenting the customers by geographic region and customer size.
If I still have more than 255 customers per segment then I was simply
going
to make each segment smaller by tweaking the customer size categories.

I'm doing this analysis mostly for myself so I can draw some conclusions
about our customers and their buying patterns, so you are correct in
saying
that legibility isn't the foremost concern.

Hopefully my questions make more sense! I'm still fairly new to Excel
charting but I'm doing my best to learn as much as I can to get the job
done.

"Jon Peltier" wrote:

It depends on what you want to show. As I asked...

Does each customer need its own series? Are you interested in
widgets per day, cumulative widgets, or something else?

Another thing you need is legibility, but your posts don't indicate that
you're too concerned with that.

What you need to think about includes:

* do I understand my client?
- do I understand the data?
- do I understand what they need to learn from the chart?
- do I know how they currently display the data?
* what are my plotted values?
* how do I segment the data?
- what are the categories? dates? parts? customer? something else?
- how do I define my series? customer? part number? classifications of
parts?

- Jon





  #14   Report Post  
Old July 30th 08, 06:06 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Adding multiple series to charts

One of your buying patterns was when customers bought widgets relative to
their first widget purchase. If you just plot by date, you'll have a big
mess. But try subtracting initial purchase data from purchase date, so you
have number of days since first purchase. This will give you a better X
variable. For your Y variable, you may leave quantity as is, or you may
divide quantity by initial purchase quantity, or you may transform it in
another way. And don't treat this as either-or. Make several charts, one for
each variation you can think of. And keep trying other variations as they
pop into your head.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"rparker" wrote in message
news
I was thinking about using an xy scatter plot because I wanted to plot time
on the x axis and number of widgets purchased on the y axis. So the x
axis
is definitely treated as a value rather than a category.

I actually don't care too much about differentiating among the 255
customers; I'm mainly looking for general trends in buying behavior. I
was
thinking that if I graph a large number of customers and juxtapose their
buying behavior together, certain patterns will emerge.

Sparklines is an interesting concept. The biggest issue I see with them
is
that they don't allow me to map many simultaneously together, which is
what
I'm primarily interested in. The aggregate is more important than the
individual customer.

"Jon Peltier" wrote:

How are you going to differentiate from among 255 customers on a chart?
What type of chart is it?

I wonder if using a set of sparklines might be what you need. These
people
have a good product:

http://www.bonavistasystems.com/Prod..._Overview.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"rparker" wrote in message
...
I want to track the widget buying history of each customer. I already
have
sales data on how many widgets have been purchased by a distinct
customer
on
a specific date. There are other helpful fields such as geographic
region,
customer size, etc. I've already been able to massage the data a bit
and
use
pivot tables so I can get statistics such as total widgets per region
per
quarter, widgets per quarter purchased by new vs. existing customers,
etc.

I'm trying to prove or disprove a hypothesis about the data; namely,
are
most widget purchases made simultaneously with the initial sale of the
first
widget or afterward? I was hoping that by graphing the widget buying
history
of a number of customers, this would become more apparent. This is why
I
planned to make each customer into its own series so I could graph out
the
customer's buying history over time.

I plan on segmenting the customers by geographic region and customer
size.
If I still have more than 255 customers per segment then I was simply
going
to make each segment smaller by tweaking the customer size categories.

I'm doing this analysis mostly for myself so I can draw some
conclusions
about our customers and their buying patterns, so you are correct in
saying
that legibility isn't the foremost concern.

Hopefully my questions make more sense! I'm still fairly new to Excel
charting but I'm doing my best to learn as much as I can to get the job
done.

"Jon Peltier" wrote:

It depends on what you want to show. As I asked...

Does each customer need its own series? Are you interested in
widgets per day, cumulative widgets, or something else?

Another thing you need is legibility, but your posts don't indicate
that
you're too concerned with that.

What you need to think about includes:

* do I understand my client?
- do I understand the data?
- do I understand what they need to learn from the chart?
- do I know how they currently display the data?
* what are my plotted values?
* how do I segment the data?
- what are the categories? dates? parts? customer? something else?
- how do I define my series? customer? part number? classifications
of
parts?

- Jon






  #15   Report Post  
Old July 30th 08, 08:19 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 5
Default Adding multiple series to charts

Thanks - I should have clarified that the x axis wouldn't show date but would
instead show days since the first purchase. I plan on calculating this as a
separate columnn and then using it to graph with. As you say, plotting by
date will result in a mess.

Is it possible to automate the process of generating all of this data
automatically? I don't think Excel will automatically create the charts I'm
looking for, so I would have to manually add each new customer series one by
one and then tweak the series formula. This is not viable from a sheer time
perspective.

The only way that comes to mind is write some sort of script to go through
the data row by row. As soon as the script detects a change in the customer
field, it will create a new series out of the rows it has previously
processed.

If this doesn't work then the only other option is to use a more flexible
graphing tool such as gnuplot or something in combination with scripts to
process and prepare the data for graphing. I would have much more
flexibility in processing and graphing the data but I was hoping that Excel
would be powerful enough to do this. It can certainly do a lot - sometimes I
think it can read my mind.


"Jon Peltier" wrote:

One of your buying patterns was when customers bought widgets relative to
their first widget purchase. If you just plot by date, you'll have a big
mess. But try subtracting initial purchase data from purchase date, so you
have number of days since first purchase. This will give you a better X
variable. For your Y variable, you may leave quantity as is, or you may
divide quantity by initial purchase quantity, or you may transform it in
another way. And don't treat this as either-or. Make several charts, one for
each variation you can think of. And keep trying other variations as they
pop into your head.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"rparker" wrote in message
news
I was thinking about using an xy scatter plot because I wanted to plot time
on the x axis and number of widgets purchased on the y axis. So the x
axis
is definitely treated as a value rather than a category.

I actually don't care too much about differentiating among the 255
customers; I'm mainly looking for general trends in buying behavior. I
was
thinking that if I graph a large number of customers and juxtapose their
buying behavior together, certain patterns will emerge.

Sparklines is an interesting concept. The biggest issue I see with them
is
that they don't allow me to map many simultaneously together, which is
what
I'm primarily interested in. The aggregate is more important than the
individual customer.

"Jon Peltier" wrote:

How are you going to differentiate from among 255 customers on a chart?
What type of chart is it?

I wonder if using a set of sparklines might be what you need. These
people
have a good product:

http://www.bonavistasystems.com/Prod..._Overview.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"rparker" wrote in message
...
I want to track the widget buying history of each customer. I already
have
sales data on how many widgets have been purchased by a distinct
customer
on
a specific date. There are other helpful fields such as geographic
region,
customer size, etc. I've already been able to massage the data a bit
and
use
pivot tables so I can get statistics such as total widgets per region
per
quarter, widgets per quarter purchased by new vs. existing customers,
etc.

I'm trying to prove or disprove a hypothesis about the data; namely,
are
most widget purchases made simultaneously with the initial sale of the
first
widget or afterward? I was hoping that by graphing the widget buying
history
of a number of customers, this would become more apparent. This is why
I
planned to make each customer into its own series so I could graph out
the
customer's buying history over time.

I plan on segmenting the customers by geographic region and customer
size.
If I still have more than 255 customers per segment then I was simply
going
to make each segment smaller by tweaking the customer size categories.

I'm doing this analysis mostly for myself so I can draw some
conclusions
about our customers and their buying patterns, so you are correct in
saying
that legibility isn't the foremost concern.

Hopefully my questions make more sense! I'm still fairly new to Excel
charting but I'm doing my best to learn as much as I can to get the job
done.

"Jon Peltier" wrote:

It depends on what you want to show. As I asked...

Does each customer need its own series? Are you interested in
widgets per day, cumulative widgets, or something else?

Another thing you need is legibility, but your posts don't indicate
that
you're too concerned with that.

What you need to think about includes:

* do I understand my client?
- do I understand the data?
- do I understand what they need to learn from the chart?
- do I know how they currently display the data?
* what are my plotted values?
* how do I segment the data?
- what are the categories? dates? parts? customer? something else?
- how do I define my series? customer? part number? classifications
of
parts?

- Jon









  #16   Report Post  
Old July 30th 08, 09:49 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Adding multiple series to charts

You may be able to use a pivot table, or a VBA approach like this:
http://peltiertech.com/WordPress/200...-chart-series/

If you're not too concerned about the specific customers, why not just plot
a single XY series of Days vs. Order Size?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"rparker" wrote in message
...
Thanks - I should have clarified that the x axis wouldn't show date but
would
instead show days since the first purchase. I plan on calculating this as
a
separate columnn and then using it to graph with. As you say, plotting by
date will result in a mess.

Is it possible to automate the process of generating all of this data
automatically? I don't think Excel will automatically create the charts
I'm
looking for, so I would have to manually add each new customer series one
by
one and then tweak the series formula. This is not viable from a sheer
time
perspective.

The only way that comes to mind is write some sort of script to go through
the data row by row. As soon as the script detects a change in the
customer
field, it will create a new series out of the rows it has previously
processed.

If this doesn't work then the only other option is to use a more flexible
graphing tool such as gnuplot or something in combination with scripts to
process and prepare the data for graphing. I would have much more
flexibility in processing and graphing the data but I was hoping that
Excel
would be powerful enough to do this. It can certainly do a lot -
sometimes I
think it can read my mind.


"Jon Peltier" wrote:

One of your buying patterns was when customers bought widgets relative to
their first widget purchase. If you just plot by date, you'll have a big
mess. But try subtracting initial purchase data from purchase date, so
you
have number of days since first purchase. This will give you a better X
variable. For your Y variable, you may leave quantity as is, or you may
divide quantity by initial purchase quantity, or you may transform it in
another way. And don't treat this as either-or. Make several charts, one
for
each variation you can think of. And keep trying other variations as they
pop into your head.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"rparker" wrote in message
news
I was thinking about using an xy scatter plot because I wanted to plot
time
on the x axis and number of widgets purchased on the y axis. So the x
axis
is definitely treated as a value rather than a category.

I actually don't care too much about differentiating among the 255
customers; I'm mainly looking for general trends in buying behavior. I
was
thinking that if I graph a large number of customers and juxtapose
their
buying behavior together, certain patterns will emerge.

Sparklines is an interesting concept. The biggest issue I see with
them
is
that they don't allow me to map many simultaneously together, which is
what
I'm primarily interested in. The aggregate is more important than the
individual customer.

"Jon Peltier" wrote:

How are you going to differentiate from among 255 customers on a
chart?
What type of chart is it?

I wonder if using a set of sparklines might be what you need. These
people
have a good product:

http://www.bonavistasystems.com/Prod..._Overview.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"rparker" wrote in message
...
I want to track the widget buying history of each customer. I
already
have
sales data on how many widgets have been purchased by a distinct
customer
on
a specific date. There are other helpful fields such as geographic
region,
customer size, etc. I've already been able to massage the data a
bit
and
use
pivot tables so I can get statistics such as total widgets per
region
per
quarter, widgets per quarter purchased by new vs. existing
customers,
etc.

I'm trying to prove or disprove a hypothesis about the data; namely,
are
most widget purchases made simultaneously with the initial sale of
the
first
widget or afterward? I was hoping that by graphing the widget
buying
history
of a number of customers, this would become more apparent. This is
why
I
planned to make each customer into its own series so I could graph
out
the
customer's buying history over time.

I plan on segmenting the customers by geographic region and customer
size.
If I still have more than 255 customers per segment then I was
simply
going
to make each segment smaller by tweaking the customer size
categories.

I'm doing this analysis mostly for myself so I can draw some
conclusions
about our customers and their buying patterns, so you are correct in
saying
that legibility isn't the foremost concern.

Hopefully my questions make more sense! I'm still fairly new to
Excel
charting but I'm doing my best to learn as much as I can to get the
job
done.

"Jon Peltier" wrote:

It depends on what you want to show. As I asked...

Does each customer need its own series? Are you interested in
widgets per day, cumulative widgets, or something else?

Another thing you need is legibility, but your posts don't indicate
that
you're too concerned with that.

What you need to think about includes:

* do I understand my client?
- do I understand the data?
- do I understand what they need to learn from the chart?
- do I know how they currently display the data?
* what are my plotted values?
* how do I segment the data?
- what are the categories? dates? parts? customer? something
else?
- how do I define my series? customer? part number?
classifications
of
parts?

- Jon











Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Charts switch from 'Series in Rows' to 'Series in Columns' Peace Charts and Charting in Excel 4 March 22nd 07 04:52 AM
XY Scatter Charts, High/Low bars, Multiple Series Kevin M. Charts and Charting in Excel 1 May 1st 06 10:16 PM
How do I plot multiple x-axis series in excel charts bdorr Charts and Charting in Excel 2 November 23rd 05 03:21 PM
Disappearing scales on bar charts with multiple series Patrick Charts and Charting in Excel 3 November 3rd 05 10:10 PM
Charts Line Types for Multiple Data Series not Printing Properly Seanb Charts and Charting in Excel 0 August 22nd 05 06:35 PM


All times are GMT +1. The time now is 10:44 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017