[Calc] Create and export chart

users
#1

Hi,

I have a Calc file which contains multiple sheets.

- every sheet contains one month of data: from May 2010 to July 2011.

- every sheet has two columes: in column A the days and in column B the
actual data.

For example:

[July 2011 Sheet]
  A B
1 07/02 75.6
2 07/03
3
4 07/04 76.4
5 07/05 76.3
6 07/06 76.2
7 07/07
8 07/08 75.6

The data entries are sometimes empty (07/07 for example), do not always
start on the 1st and there are some empty rows in between.

Problem 1: I want to create a chart of all sheets/months. What do I have
to enter for Date Range in step 2 of the chart wizard (pre-selected is
$'July 2011'.$A$1:$B$3)?

Problem 2: I'd like to automatically create this chart every day and
export it to a PNG file. Is that doable via LibreOffice Calc?

Thanks in advance, Thorsten

#2

Hi :slight_smile:
This sort of summary report is usually easier in a proper database program such
as Base. Coincidentally Base can read Calc/Excel files so you wouldn't have to
"transfer" any data, just use what you have.

Personally i would probably stick to Calc and insert a new sheet that calculated
summaries from all the other sheets and then use that as the basis for a chart.
You could even create those 2 sheets in another file that just referenced the
original although that tends to get messy as it asks for permission to update
"links" to the original and complains if it can't find the file. I'm not sure
how to create charts in Base.

The bit i would be really stuck over is exporting that as a png or any other
image format. Perhaps Draw might help? I have never used Draw so i don't know
what it can do. The chart in Calc would be constantly updated. If any new data
was entered into the other sheets the chart would reflect that.
Regards from
Tom :slight_smile:

#3

* Tom Davies (Wed, 13 Jul 2011 18:42:47 +0100 (BST))

Personally i would probably stick to Calc and insert a new sheet that
calculated summaries from all the other sheets and then use that as
the basis for a chart.

So you cannot create charts in one sheet that takes data from another
sheet from within the same Calc file? There is no $'ALL SHEETS'.$A$1:$B
$3)??

If not how can I create a sheet that includes all the other sheets'
values?

Thorsten

#4

Hi :slight_smile:
You probably can do it all in one sheet but 2 would probably be easier to
trouble-shoot later if you ran into problems.

What type of chart are you hoping to end-up with? Do you want a single line
representing the entire year or do you want to compare months against each
other?

For the latter option my way would be fairly clumsy. On the calculations sheet
the A column would give the dates from Jan & B the data from Jan. C & D would
be Feb. E & F would be March. I would set-up the first row and then drag the
equations down as far as required. In A an equation such as

=Sheet1.A1

unless you have renamed Sheets to the name of the month in which case something
like
=Jan.A1
drag this to copy into B. C would need a new equation
=Sheet2.A1
Now if you select those 3 cells you can probably drag-fill all the remaining
columns, then drag-fill down.

Hmm, you probably want a graph showing the whole year in a line? I would be
fairly clumsy with that too tbh. Someone is likely to have a much better answer
in a few hours but it would help to know what you are aiming for.

Regards from
Tom :slight_smile:

#5

Hi Thorsten,

Thorsten Kampe schrieb:

Hi,

I have a Calc file which contains multiple sheets.

- every sheet contains one month of data: from May 2010 to July 2011.

- every sheet has two columes: in column A the days and in column B the
actual data.

For example:

[July 2011 Sheet]
   A B
1 07/02 75.6
2 07/03
3
4 07/04 76.4
5 07/05 76.3
6 07/06 76.2
7 07/07
8 07/08 75.6

The data entries are sometimes empty (07/07 for example), do not always
start on the 1st and there are some empty rows in between.

Problem 1: I want to create a chart of all sheets/months. What do I have
to enter for Date Range in step 2 of the chart wizard (pre-selected is
$'July 2011'.$A$1:$B$3)?

First create a chart from only one sheet. Set the chart in edit mode and then from the context or Format menu use item 'Date Ranges'. Goto tab 'Data Series' and add the data series of the other sheets one by one. Because your series start not in the same cell address, you cannot use the simplified data range on tab 'Data Range'.
Select the ranges including empty cells, as large as they might become. In the data series property you can determine how empty cells are handled.

Problem 2: I'd like to automatically create this chart every day and
export it to a PNG file. Is that doable via LibreOffice Calc?

For really 'automatically' you will need a macro.

kind regards
Regina

#6

You can see if it is possible to select a cuboid, but that may or may not provide what you want.

Also, there is an interesting problem of maintaining the correspondence between the column A entries and the column B entries if that is important to you.

A cuboid is a range that starts on one sheet (say the upper left or the left-most column) and ends on the final sheet (say the lower right or the right-most column).

This is a provision of OpenFormula in ODF 1.2 but I don't know what the implementation is in LibreOffice.

A lot of how this works really depends on what it is you want in the chart - then you can figure out how to boil the date from the other sheets down to produce the data that is exactly the basis for the chart.

I don't want to speculate farther because it is not clear what can work without understanding what you want as the chart data.

- Dennis

#7

chart
Date sent: Wed, 13 Jul 2011 20:09:50 +0200
Send reply to: users@global.libreoffice.org

* Tom Davies (Wed, 13 Jul 2011 18:42:47 +0100 (BST))
> Personally i would probably stick to Calc and insert a new sheet that
> calculated summaries from all the other sheets and then use that as
> the basis for a chart.

So you cannot create charts in one sheet that takes data from another
sheet from within the same Calc file? There is no $'ALL SHEETS'.$A$1:$B
$3)??

If not how can I create a sheet that includes all the other sheets'
values?

Thorsten

I was playing with this, and couldn't figure a way to get data from
different sheets into a chart, but did come up with something
interest.

In a single sheet, I put the dates in column A from 1/1/2011 thru
12/31/2011. Used =randbetween(1,10) in column B just to have
some data.

Then did an XY graph using the Block, and that showed the whole
years of data.

Then went into the X-axis Scale, and turned off automatic, and set
the minimum to 3/1/2011 and maximum to 3/30/2011 as a test,
and got a chart with that data range..

Haven't done much with Calc, so there might be a way to
automate the process?

#8

* Dennis E. Hamilton (Wed, 13 Jul 2011 13:04:23 -0700)

A lot of how this works really depends on what it is you want in the
chart - then you can figure out how to boil the date from the other
sheets down to produce the data that is exactly the basis for the
chart.

I don't want to speculate farther because it is not clear what can
work without understanding what you want as the chart data.

I'd like to create a line chart with the values from columm A (the
dates) as the X axis and the values from column B for the Y axis and
this from all sheets in the spreadsheet. As if I would not have used
multiple sheets but would have put all the data just in one.

Thorsten

#9

Hi Thorsten,

Thorsten Kampe schrieb:

* Dennis E. Hamilton (Wed, 13 Jul 2011 13:04:23 -0700)

A lot of how this works really depends on what it is you want in the
chart - then you can figure out how to boil the date from the other
sheets down to produce the data that is exactly the basis for the
chart.

I don't want to speculate farther because it is not clear what can
work without understanding what you want as the chart data.

I'd like to create a line chart with the values from columm A (the
dates) as the X axis and the values from column B for the Y axis and
this from all sheets in the spreadsheet. As if I would not have used
multiple sheets but would have put all the data just in one.

OK, that is clearer now. You have to use the tab "Data Series". For the role "X-Values" you would write $Sheet1.A2:$Sheet3.A6 and for the role "Y-Values" you would write §Sheet1.B2:$Sheet3.B6, for example.
The chart is drawn as if the data are $Sheet1.A2:B6 followed by &Sheet2.A2:B6 followed by $Sheet3.A2:B6.

Kind regards
Regina

#10

* Regina Henschel (Wed, 13 Jul 2011 22:36:31 +0200)

Thorsten Kampe schrieb:
> I'd like to create a line chart with the values from columm A (the
> dates) as the X axis and the values from column B for the Y axis and
> this from all sheets in the spreadsheet. As if I would not have used
> multiple sheets but would have put all the data just in one.

OK, that is clearer now. You have to use the tab "Data Series". For
the role "X-Values" you would write $Sheet1.A2:$Sheet3.A6 and for the
role "Y-Values" you would write §Sheet1.B2:$Sheet3.B6, for example.
The chart is drawn as if the data are $Sheet1.A2:B6 followed by
&Sheet2.A2:B6 followed by $Sheet3.A2:B6.

That sounds good but the result doesn't look right. See the chart for
multiple sheets[1] versus the chart I created by copying the data of all
sheets into one sheet[2].

Thorsten
[1]
<http://www.screencast.com/users/thorstenkampe/folders/Default/media/98e
43905-12b5-4ff8-9ac7-f0f06e365d5d>
[2]
<http://www.screencast.com/users/thorstenkampe/folders/Default/media/642
d5074-ba43-46ba-83c9-ee09623dadba>

#11

Hi :slight_smile:
As i see it the problems are
1. to get the full years data onto one sheet
2. remove any rows where there is no data for a particular day
Making the chart based on that would be very easy
4. exporting as png dynamically

I'm beginning to think my initial suggestion of using Base might not have been
as bad as i first thought. A simple Query table would add all the values and
dates from the various sheets. I would use another Query to remove the blanks
but that could be done in the same Query. I've not used Base to create charts
but i suspect it would be easier to use that chart wherever it's needed.

Is the png needed for displaying on a web-page?
Regards from
Tom :slight_smile:

#12

* Thorsten Kampe (Thu, 14 Jul 2011 03:26:01 +0200)

* Regina Henschel (Wed, 13 Jul 2011 22:36:31 +0200)
> Thorsten Kampe schrieb:
> > I'd like to create a line chart with the values from columm A (the
> > dates) as the X axis and the values from column B for the Y axis
> > and this from all sheets in the spreadsheet. As if I would not
> > have used multiple sheets but would have put all the data just in
> > one.
>
> OK, that is clearer now. You have to use the tab "Data Series". For
> the role "X-Values" you would write $Sheet1.A2:$Sheet3.A6 and for
> the role "Y-Values" you would write §Sheet1.B2:$Sheet3.B6, for
> example. The chart is drawn as if the data are $Sheet1.A2:B6
> followed by &Sheet2.A2:B6 followed by $Sheet3.A2:B6.

That sounds good but the result doesn't look right. See the chart for
multiple sheets[1] versus the chart I created by copying the data of
all sheets into one sheet[2].

I finally figured it out. For reasons I don't fully understand yet, it
was the wrong chart type: Line > Lines Only instead of XY (Scatter) >
Lines Only. I switched to the latter and it started looking right[1]
(and the same as if using just one sheet).

actually wanted a Lines chart and not a XY (Scatter) with lines only[2].

Anyway, thanks to everyone who helped me in this thread; and especially
thanks to Regina, the queen of charts!

Thanks, Thorsten
[1] http://www.screencast.com/t/9jQqD8oGxD
[2] http://home.gna.org/pychart/doc/module-line-plot.html

#13

Am 13.07.2011 22:26, Thorsten Kampe wrote:

I'd like to create a line chart with the values from columm A (the
dates) as the X axis and the values from column B for the Y axis and
this from all sheets in the spreadsheet. As if I would not have used
multiple sheets but would have put all the data just in one.

Thorsten

Thank you for clarification. Forget my first suggestion about the data pilot and try scenarios. A scenario is a range where you can easily switch the content (show January data in this range, show February data in the same range,...). The formatting, the formulas and the chart remain the same.

Example scenarios:

#14

Hi :slight_smile:
Congrats :slight_smile: Nicely found! Thanks for the useful links.
+1 about Regina. Charts isn't even her main thing !
Regards from
Tom :slight_smile:

#15

Hi Thorsten,

Hi,

I have a Calc file which contains multiple sheets.

- every sheet contains one month of data: from May 2010 to July 2011.

- every sheet has two columes: in column A the days and in column B the
actual data.

This sounds very much like the rainfall spreadsheet I use currently.
Currently it has 19 sheets and it covers the entire year as well as 5
years history.

I have attached it for you to view and adapt to your need.

I hope it assists you

Regards
Hylton