Line chart in LibreOffice - data in rows (type, x, y) rather than in a 2-input table

Question

How do I create a line chart in LibreOffice Calc if the data is organised in 3-tuples rather than in a two-input table?

Example:

Banana      Jan   10
Banana      Feb   20
Banana      Mar   30
Banana      Apr   40
Orange      Jan   13
Orange      Feb   16
Orange      Mar   24
Orange      Apr   27
Grape       Jan   73
Grape       Feb   11
Grape       Mar   22
Grape       Apr   21

I would like to have (in a single graph) one line per fruit, with the months in the x axis and the values of the third column in the y axis.

This would be straightforward if the data were organised in a two-input table, such as

         Jan  Feb  Mar  Apr
       +-------------------
Banana |  10   20   30   40
Orange |  13   16   24   27
Grape  |  73   11   22   21

However, I have a very large set of data in the first format (3-tuples). I've tried many of the options given in the graph wizard, but none of them has worked. If the data were small, I could easily generate a two-input table from the data by using a simple script, but the tables are huge, so I would like to know if there is some way to create the chart without rearranging the data.

Thank you

EDIT: Also, if someone knows how tables like the first one are called, please tell me. If I knew that, I could have done a better search on the internet before asking...

Answer 1

Converting a simple table (as your table 1) to a contingency table (as your table 2) is a standard task for a pivot table.

Depending on your data, it may be useful to prepare the contents before creating the pivot table:

• it's useful to have column names, which facilitates the pivot table definition;
• the month names should be DATE values, otherwise LO Calc will try to sort them alphabetically.

So, i will start with a slightly modified data set:

^{Notice the "real" date value for the month entry - you achieve this by entering the date and format the cell as Date with format code MMM.}

Now, select A1:C13 and select Menu Insert -> Pivot Table. Confirm to create the pivot table from the current selection. In the pivot table definition dialog ...

... drag the fields from the "Available Fields" list into Row / Column / Data Fields list as follows:

• "Type" into "Row Fields,
• "Month" into "Column Fields" and
• "Count" into "Data fields":

After dragging the fields, the dialog should look like this:

That's it - hit OK. The resulting table looks as follows:

You can disable the totals columns / rows in the pivot table layout dialog. The pivot table is still "connected" with your source data, so modifying your "simple table" data will be reflected in the pivot table (after a manual refresh).

Additionally, you can use the pivot table to create a chart in a simple way. The result may look as follows:

Modifications of the original "simple" table will affect the chart, too (using the pivot table as "proxy").