Search code examples
excelchartslinechartlibreoffice-calc

Creating a line chart from data with multiple date rows per date?

I'm struggling to create a line chart with three lines in Excel or Libreoffice Calc. I would like to have the X axis be the date and the y axis for the counts.

I have my data (the result of a SQL query) in three columns, it looks like this (there are more rows):

date        version count
10.07.19    2.10    1734
10.07.19    3.2       52
10.07.19    3.11    1224
11.07.19    2.10    1897
11.07.19    3.2      108
11.07.19    3.11    1238

I would like to create a line diagram with three lines, one for each possible value of the "version" column. From my limited knowledge of spreadsheet programs i think i need all "count" values on a single row to create a diagram. So I have two questions:

  • Can I create a diagram without having all counts belonging to one date on a single line?

  • If not, how do I get those counts on a single line? Something with filtering?

Thanks!

Solution

  • Create a Pivot table, with the date as the rows, and the version as the columns, and the Data fields the count.

    count           version         
date            2.1     3.11    3.2     Total Result
10.07.19        1734    1224    52      3010
11.07.19        1897    1238    108     3243
Total Result    3631    2462    160     6253

    Then you can generate the chart from this table.