OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Dynamic charts using defined names, and trendlines in charts

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
richbl
General User
General User


Joined: 05 May 2007
Posts: 8

PostPosted: Tue Jul 17, 2007 7:48 pm    Post subject: Dynamic charts using defined names, and trendlines in charts Reply with quote

Hello all,

I've been using OOO for about a year, and for about 90% of my work, all the suite applications fit the bill rather nicely.

Every once in a while, though, I come across an MS Office document that I cannot seem to convert properly.

Most recently, I have an Excel workbook with three worksheets. I use this workbook to track blood pressure data, and chart that data accordingly.

So far, there are two issues that I've run into with this workbook:

A) In a line chart, is there a way to generate a trendline that is the functional equivalent to Excel? My research suggests that this feature does not yet exist in OOO Calc.
B) In Excel, I'm able to generate a chart that tracks a rolling past 6-month dataset using defined names. In OOO Calc, I cannot seem to find a way to do this. Is this possible?


For reference, I've place the original XLS file, called blood_pressure.xls up on www.businesslearninginc.com/files/blood_pressure.xls.

In Excel, you'll notice trendlines in any of the three charts. On the worksheet entitled "BP (Last 6 months)," you'll see the 6-month rolling average of blood pressure data. In OOO Calc, neither of these aspects are functional.

Please advise.

Thanks much.

Rich
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Wed Jul 18, 2007 7:34 am    Post subject: Reply with quote

First, to get a trendline, you must have an X-Y chart (aka a scatter chart), and not a line chart, so you need to change the chart type. (With a line chart, the items along the horizontal axis are just labels and can be anything, words, numbers, etc., whereas with an XY chart, they must be numbers so they can be plotted correctly.)

Second, I often find editing charts in Calc a real pain, and I often re-create one rather than try to edit one that's no good.

There seem to be various ways to put a trendline in, such as right-click the chart, Edit, then click the series (if you get the chart wall, keep trying!) then when the series is selected, right-click on it, Object Properties, Statistics tab, then you can add a trendline.

You can't display the equation - sorry! Use the spreadsheet functions SLOPE( ) and INTERCEPT( ) if you just want a straight line equation in the spreadsheet, not on the chart.

At a quick glance your moving averages at least gave answers. Did you mean Calc wouldn't do them at all, or it did them wrong?
Back to top
View user's profile Send private message
richbl
General User
General User


Joined: 05 May 2007
Posts: 8

PostPosted: Wed Jul 18, 2007 7:47 am    Post subject: Reply with quote

Thanks RickRandom for the response.

I'm aware of using an X-Y chart with trendlines. Unfortunately, an X-Y chart really won't work for my needs.

As for your question regarding moving averages, the calculations work fine in Calc, the problem is that the chart that uses moving averages does not seem to work.

To confirm this, go to the worksheet entitled "BP _Last 6 months" and you should see a chart of data taken from the most recent 6-month period. In OOO Calc, the chart is empty. So, for some reason, the data didn't carry from MS Excel into OOO Calc. I'm guessing that, since this chart makes use of defines names, that OOO Calc doesn't know how to use defined names in charts.

I hope this clarifies things a bit more.

thanks
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Jul 18, 2007 8:02 am    Post subject: Reply with quote

Yes, charts and some other items can not deal with calculated range names like your
Code:

OFFSET($Data.$A$510;-(SUMPRODUCT((($Data.$A$4:$A$509)>=(DATEVALUE(TEXT($Data.$A$509;"yyyy-mm-dd"))-180))*(1)));0;(SUMPRODUCT((($Data.$A$4:$A$509)>=(DATEVALUE(TEXT($Data.$A$509;"yyyy-mm-dd"))-180))*(1)));1)

Some year ago I got this working (can't find the thread in this forum) with a datasource import, using the data sheet as external datasource, and a set of queries which create automatically resized database ranges on import.
This requires Menu:Data>Refresh to re-import and resize the ranges.
Back to top
View user's profile Send private message
richbl
General User
General User


Joined: 05 May 2007
Posts: 8

PostPosted: Wed Jul 18, 2007 8:12 am    Post subject: Reply with quote

Villeroy wrote:
Yes, charts and some other items can not deal with calculated range names like your
Code:

OFFSET($Data.$A$510;-(SUMPRODUCT((($Data.$A$4:$A$509)>=(DATEVALUE(TEXT($Data.$A$509;"yyyy-mm-dd"))-180))*(1)));0;(SUMPRODUCT((($Data.$A$4:$A$509)>=(DATEVALUE(TEXT($Data.$A$509;"yyyy-mm-dd"))-180))*(1)));1)

Some year ago I got this working (can't find the thread in this forum) with a datasource import, using the data sheet as external datasource, and a set of queries which create automatically resized database ranges on import.
This requires Menu:Data>Refresh to re-import and resize the ranges.

Thanks for your response, Villeroy.

So, this is possible to do? Or are you saying that it is not possible with charts in OOO Calc?

I don't think I completely understood your response.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Jul 18, 2007 8:20 am    Post subject: Reply with quote

Open a new spreadsheet.
Hit F4, pick the table of the "Bibliography" datasource from the left pane of the datasource browser and drag it onto any cell.
Call Data>Select... and notice the named database range "Import1".
Add some records to the bibliography and call Data>Refresh
Remove some records from the bibliography and call Data>Refresh
A chart, bound to "Import1" should resize with the imported data.

Edit:
My currently openened sheet has a formula that helps me to select the data of a named range "criteria" below header in order to clear it's contents with a click and a key stroke.
=HYPERLINK(ADDRESS(ROW(criteria)+1;COLUMN(criteria);1;"#")&":"&ADDRESS(ROW(criteria)+ROWS(criteria)-1;COLUMN(criteria)+COLUMNS(criteria)-1))
You can copy the resulting address-string and paste it into the data range dialog of a chart. This makes it slightly easier to adjust the chart range manually.
Another way to handle growing/shrinking lists is insertion of new rows for each new input. I use custom shortcuts Ctrl+Ins and Ctrl+Del to insert and remove rows. All formula references and charts will (should?) adjust in this case.
Back to top
View user's profile Send private message
robina
OOo Advocate
OOo Advocate


Joined: 24 Mar 2005
Posts: 275

PostPosted: Wed Jul 18, 2007 8:46 am    Post subject: Reply with quote

Charts and trendlines... try using the new chart module, available in "developer" OOo versions since last May.
http://download.openoffice.org/680/index.html?intcmp=1235
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Jul 18, 2007 9:30 am    Post subject: Reply with quote

Just tried with import ranges. I can't get it to work. A macro can do the trick. The new chart module may be able do the trick.
Back to top
View user's profile Send private message
richbl
General User
General User


Joined: 05 May 2007
Posts: 8

PostPosted: Thu Jul 26, 2007 7:22 pm    Post subject: Reply with quote

robina wrote:
Charts and trendlines... try using the new chart module, available in "developer" OOo versions since last May.
http://download.openoffice.org/680/index.html?intcmp=1235


I can confirm this. With the "Developer Snapshot 2.X" trendlines are available, albeit limited to regression curves.

Thanks.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group