Linear Graph with X,Y data

classic Classic list List threaded Threaded
9 messages Options
jmadero jmadero
Reply | Threaded
Open this post in threaded view
|

Linear Graph with X,Y data

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.


Best,
Joel

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
jmadero jmadero
Reply | Threaded
Open this post in threaded view
|

Re: Linear Graph with X,Y data

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

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Pedro Pedro
Reply | Threaded
Open this post in threaded view
|

Re: Linear Graph with X,Y data

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
Regina Henschel Regina Henschel
Reply | Threaded
Open this post in threaded view
|

Re: Linear Graph with X,Y data

In reply to this post by jmadero
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



--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
jmadero jmadero
Reply | Threaded
Open this post in threaded view
|

Re: Linear Graph with X,Y data

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 ;) 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 :) Thanks for the
detailed explanation.


Best,
Joel

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
jmadero jmadero
Reply | Threaded
Open this post in threaded view
|

Re: Linear Graph with X,Y data

In reply to this post by Pedro
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 :) That being said, I might have to go back to it.

Thanks for taking the time to reply!

Best,
Joel

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

libreoffice-ml.mbourne libreoffice-ml.mbourne
Reply | Threaded
Open this post in threaded view
|

Re: Linear Graph with X,Y data

In reply to this post by jmadero
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 ;) 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.


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Linear Graph with X,Y data

In reply to this post by jmadero
At 11:40 17/02/2016 -0800, Joel Madero wrote:
>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).

In row 1 of a spare column, enter:
=DATEVALUE(OFFSET(A$1;MOD(ROW()-1;12)+1;0)&OFFSET(A$1;0;INT((ROW()-1)/12)+1))
and fill down the column as necessary. The first OFFSET(), using
MOD(), harvests the month names and the second OFFSET(), using INT(),
harvests the year numbers; DATEVALUE() then assembles the monthly
date values from the concatenation of the two. (It appears that no
intervening space is necessary.)

In row 1 of the following column, enter:
=OFFSET(A$1;MOD(ROW()-1;12)+1;INT((ROW()-1)/12)+1)
and fill down similarly. This formula harvests the currency amounts.

You now have two columns from which you can readily create your
chart. The two columns used intermediately can be hidden or placed on
another sheet (or both) if preferred. Since the new columns are
derived from your original table, not simply a copy of its values,
you can continue to modify data in the table and see it reflected in the chart.

I'm attaching a copy including a sample chart, though I imagine only
you will see this.

I trust this helps.

Brian Barker

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Linear Graph with X,Y data

In reply to this post by jmadero
At 13:50 17/02/2016 -0800, Joel Madero wrote:
>Pedro Pedlino wrote:
>>(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

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


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted