Linear Graph with X,Y data

users
#1

Hi All,

Please see this file:
https://drive.google.com/file/d/0B2kdRhc960qdV2dTVUh5aWZIUWc/view?usp=sharing

I'm hoping to create a linear graph from January of 2007 -> Current
Month using this data. I can't think of a way to do this without putting
the data in a different format (possible but not ideal). Can anyone
think of a different solution for a single line chart over time (I don't
want a different line per year or month). Thanks in advance.

Apologies if this shows up twice on list - I forgot I unsubscribed int
he past.

Best, Joel

#2

Hi Joel

jmadero wrote

I'm hoping to create a linear graph from January of 2007 -> Current
Month using this data. I can't think of a way to do this without putting
the data in a different format (possible but not ideal). Can anyone
think of a different solution for a single line chart over time (I don't
want a different line per year or month).

In my experience this is simply not possible. To have a linear graph the
X-values have to increase (in this case with year). You have 12 text values
on the X-axis.
You really have to convert this to a two column structure (BTW if you make
the first date value 1/15/2007 and the second 2/15/2007 you can simply drag
the X values all the way down and LO is smart enough to always use the 15th
of every month)

If someone knows a different solution, I'm also interested in learning!

Regards,
Pedro

#3

Hi Joel,

Joel Madero schrieb:

Hi All,

Please see this file:
https://drive.google.com/file/d/0B2kdRhc960qdV2dTVUh5aWZIUWc/view?usp=sharing

I'm hoping to create a linear graph from January of 2007 -> Current
Month using this data. I can't think of a way to do this without putting
the data in a different format (possible but not ideal). Can anyone
think of a different solution for a single line chart over time (I don't
want a different line per year or month). Thanks in advance.

Apologies if this shows up twice on list - I forgot I unsubscribed int
he past.

Technically it is possible, but it is cumbersome and instable. I would not go that way, but generate a one-dimensional list from the data. With such list, you can use a real time axis.

Now the technical part:

Enter category (first) and value (second) manually to the fields in the data range dialog.

For example:

in Category (for 3 years)
$Sheet1.$A$2:$A$13;$Sheet1.$A$2:$A$13;$Sheet1.$A$2:$A$13
Notice, the same range is repeated.

in Values (for 3 years)
$Sheet1.$B$2:$B$13;$Sheet1.$C$2:$C$13;$Sheet1.$D$2:$D$13

Notice, the parts are connected with semicolon.

Disable all automatic from the x-axis formatting. Set the type "Date" and enter the needed min, max and intervals. Set label to 90°. For the y-axis disable "source format" at tab Number and set to 0 decimals.

Kind regards
Regina

#4

Hi Regina and Pedro,

Technically it is possible, but it is cumbersome and instable. I would
not go that way, but generate a one-dimensional list from the data.
With such list, you can use a real time axis.

That's what I figured - so I'll have to have a ghost list somewhere
(probably column ZZ way to the right) that is generated from the x,y
list.... Perhaps time for an enhancement request :wink: I don't think that
this set up is all that uncommon for demonstrating a table and wanting
to have an accompanying chart.

Now the technical part:

Enter category (first) and value (second) manually to the fields in
the data range dialog.

For example:

in Category (for 3 years)
$Sheet1.$A$2:$A$13;$Sheet1.$A$2:$A$13;$Sheet1.$A$2:$A$13
Notice, the same range is repeated.

in Values (for 3 years)
$Sheet1.$B$2:$B$13;$Sheet1.$C$2:$C$13;$Sheet1.$D$2:$D$13

Notice, the parts are connected with semicolon.

Disable all automatic from the x-axis formatting. Set the type "Date"
and enter the needed min, max and intervals. Set label to 90°. For the
y-axis disable "source format" at tab Number and set to 0 decimals.

I'm going to give this a try just out of curiosity :slight_smile: Thanks for the
detailed explanation.

Best,
Joel

#5

Hi Pedro,

In my experience this is simply not possible. To have a linear graph the
X-values have to increase (in this case with year). You have 12 text values
on the X-axis.
You really have to convert this to a two column structure (BTW if you make
the first date value 1/15/2007 and the second 2/15/2007 you can simply drag
the X values all the way down and LO is smart enough to always use the 15th
of every month)

I had this kind of a setup before and it just bothered me that it wasn't
entirely accurate. My data is not pertaining to a particular day, it's a
summary of the months so having the data show the 15th of the month was
just annoying me :slight_smile: That being said, I might have to go back to it.

Thanks for taking the time to reply!

Best,
Joel

#6

Joel Madero wrote:

Hi Regina and Pedro,

Technically it is possible, but it is cumbersome and instable. I would
not go that way, but generate a one-dimensional list from the data.
With such list, you can use a real time axis.

That's what I figured - so I'll have to have a ghost list somewhere
(probably column ZZ way to the right) that is generated from the x,y
list.... Perhaps time for an enhancement request :wink: I don't think that
this set up is all that uncommon for demonstrating a table and wanting
to have an accompanying chart.

You may be able to put the "ghost list" in a separate sheet, rather than in a column off to the far right, if you want to keep it out the way of the main data. Then have the chart use the data from that other sheet. I don't think the chart has to be one the same sheet as its data.

Having a quick look, I just noticed you can hide sheets, if you want it really out of sight!

Mark.

#7

This shouldn't be a problem. In order for the chart to make sense, the date values need to be exactly that - not merely pieces of text that look like dates. If one month was missing from your data, you would want the remaining values to be spaced properly - and this would happen only with true dates, not with text values.

But there is no need for the day numbers (whatever they are) to show in the chart: the dates can be formatted as you wish. In my example chart, I've chosen "MMM YYYY" as the date format, which shows any day in this month as "Feb 2016". You can arrange chart axis formatting in either of two ways: either inherited from the formatting in the data ranges or set in the chart specification itself.

I trust this helps.

Brian Barker