Why can't I change the X- and Y-axes in a pivot chart?

I’m doing some work on OECD statistics for hospital care. The data is downloaded from stats.oecd.org in CSV format, then opened in Calc. Then I use Pivot tables to make the data useable, and this is fine.
My pivot tables are pretty standard stuff, I use filters to get the information I actually want, then the rows are by country and the columns by year.
I want to produce a line chart for hospital beds per 1000 of population where the number of beds is the y-axis, the year is the x-axis, and each line is a country (pretty standard and straightforward). I can do this just creating an ordinary chart and defining the x and y ranges myself, but when I create a pivot chart I always get the country along the x-axis and each year is a separate line. Since the “data range” option is greyed out when creating the chart I can’t see any way to get the result I want.
I’ve attached the file in question with two charts on the sheet “Pivot_table_hospital_beds_1”
OECD Health Statistics.ods


  • change the pivot table definition to:

image description

  • change chart to show lines

and see the following modified file:

Hope that helps.

In case the OP or anyone else encountering this problem is interested in a solution that preserves linear time scale even with non-equidistant selections of years, while keeping pivot table updates and filters usable and effective to the chart, here’s another sample file:
OECD-Health-Statistics-Modified2.ods (518.1 KB)
It uses a plain chart (rather than special pivot chart) with the pivot table, as described in this workaround to the known LO bug no. 125750 .

p. s. (side note): Whilst ICUs are included in “curative (acute care) beds”, that doesn’t hold the other way around. There are various other kinds of curative (acute care) beds, as well, see e. g. https://ec.europa.eu/eurostat/cache/metadata/Annexes/hlth_res_esms_an7.pdf . Hence, the curative (acute care) capacity is much greater than the count of ICUs, which each country tracks in separate statistics.