Line chart in Excel - trendline incomplete

  • Thread starter Thread starter sbitaxi
  • Start date Start date
S

sbitaxi

I have created a line chart that references 3 different sets of data.

Period (52 one week periods, placed on the category Axis)

Fulfillment Totals (total orders received during the year, with totals
per week long period - in the Data area)

Promotional Events (in the Series Axis - these are specific promotional
events that occurred during the 52 one week periods)

When I don't incorporate the Promotional Events, the line is complete,
as soon as I add those into the chart, the line becomes broken, and I
cannot seem to find a way to complete it. Including the promotional
events creates an important set of check points on the line chart that
I want in there to identify when certain promotions happened to
influence orders.

Why does the line dissapear? Any thoughts or suggestions? I may not be
very clear on my description, I am not exceptionally familiar with
creating charts in Excel.


Any support is exceptionally appreciated.
 
Is this a line chart or an XY chart?
Do you use one line in each case or are there three different lines
overlapping each other?
 
Hi BJ,

I definitely was not very clear on this.

It's a line chart.

the dates appear along the bottom of the chart

the orders are in the data area, showing peaks at different points
during the series.

In the legend are the Events, each with a different shape or colour
associated with it that appears on the line showing when they occured.

There is only one line, no overlapping. The quantity of orders relate
to the dates, the events also relate to dates.


hope this makes it a little more clear.
 
I appologise for restating what you have already stated I I nned to
understand what you are doing

You start with one line showing orders against dates.
Second you add pronotional events in a picture form against the dates
I think you have a different picture for each promotional event.
As such there is a different data series to represent the Promotional event
With the X value being the Date.
Is the Y value the same number as the order for that date?
If not what is it?
When you click on the figure representing the promotional event and
goto the patterns tab does it show any line with it?
Does the line disapear or just have sections disapear around the figure?
If just around the figure, did you generate the figures with a white
background which could hide the line behind it?
 
No problem BJ, we are doing this virtually and describing things in
text is much more challenging than just showing the image.

Yes, I start with one line, orders against dates - with markers
displayed at each data value (as MSExcel describes the chart format)
Second - yes, promotional events against the dates. The pictures are
the markers on the line, they change for each different promotional
event.

Y is the value of the order on that date.

Yes, when I click on the figure representing the promotional event,
there is a line. I attempted to change the colour of the line so it
matches the rest of the lines.

Only sections of the line dissapear. It seems to be dependant on the
marker. If the marker is the same, there is a line, if it is different,
there is no line connecting them. There is no white background. The
line is not broken in quite that way. It has larger gaps, starting at
one marker and going to the next. In some instances where there are
several markers that are different, there is no line for quite some
time, then it restarts at the default marker, and so long as the marker
is the same following, the line continues.


Thanks BJ.
 
I recommend you do the following
assuming your data is in columns, if in rows just transpose the suggestions
set up column A with your time line
Set up column B with your orders
Set up columns C- whatever for the number of different promotional type
activities
that you have.
in columns C- enter the order number for just those dates which have the
promotional activity.
chart all.
for each of the promotional activities select none for line and the marker
you want.
for the order series select none for marker and the line color you want.

There are other methods of doing what I think you want, but this is what I
feel is the easiest to explain. (and hopefully to understand)
 
Great. I'll try it out and let you know how it works out. Thank you for
the help. I considered myself to be quite competent with excel, until I
tried charts.....
 
I realize you are having a conversation with someone else -- and, yes,
I've read those posts -- but you may want to clarify some stuff.

How are your data organized? Is the date+order information in two
columns that you plot as one series in a chart? Then, how is the
promotional event data organized?

Date Order Promo Promo description
1/1 10
1/2 15 1 dfdoidfd
2/1 30 1 mmkjuyir
2/15 10
3/1 12

etc.

or is it something else?

The typical way of doing what you want (and this is similar to stock
charts) would be to use the promo as a data label for the order series.
Suppose you plot only the date+order data. Then, use XY Chartlabeler
(www.appspro.com) or Chart Tools (www.j-walk.com) to add the 'promo
description' as the data labels for the plotted series.

If that appeals to you, you should also check out Hover Chart Label
(http://www.tushar-
mehta.com/excel/software/chart_hover_label/index.html). This add-in
also includes a data label capability.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005
 
Back
Top