Bug 133423 - Implement chart:regression-moving-type
Summary: Implement chart:regression-moving-type
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Chart (show other bugs)
Version:
(earliest affected)
7.0.0.0.alpha1+
Hardware: All All
: medium enhancement
Assignee: Samuel Mehrbrodt (allotropia)
URL:
Whiteboard: odf target:7.2.0
Keywords:
Depends on:
Blocks: Chart-Enhancements
  Show dependency treegraph
 
Reported: 2020-05-26 19:28 UTC by Regina Henschel
Modified: 2021-06-04 14:57 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Example with screenshot from Gnumeric (26.57 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-05-26 19:28 UTC, Regina Henschel
Details
Manually calculate the regression curves (26.21 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-02-10 13:26 UTC, Regina Henschel
Details
Compare manually calculated moving average with chart feature (40.92 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-03-06 22:29 UTC, Regina Henschel
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Regina Henschel 2020-05-26 19:28:49 UTC
Created attachment 161310 [details]
Example with screenshot from Gnumeric

ODF 1.3 has the property chart:regression-moving-type with values "prior", "center" and "averaged-abscissa". LibreOffice 7.0 does not know this property, but always calculates as "prior", which fortunately is the default value for this property.

But especially "averaged-abscissa" is much better than "prior". Gnumeric can generate moving-average regression curves of type "averaged-abscissa". The attached file contains a screenshot from Gnumeric.

My feature request is, to implement the property chart:regression-moving-type.
Comment 1 Samuel Mehrbrodt (allotropia) 2021-02-09 08:22:58 UTC
(In reply to Regina Henschel from comment #0)
> LibreOffice 7.0 does not know this
> property, but always calculates as "prior", which fortunately is the default
> value for this property.
> 
> But especially "averaged-abscissa" is much better than "prior". 

Should "averaged-abscissa" be the default for new documents?
Comment 2 Regina Henschel 2021-02-09 10:45:05 UTC
(In reply to Samuel Mehrbrodt (allotropia) from comment #1)
> (In reply to Regina Henschel from comment #0)
> > LibreOffice 7.0 does not know this
> > property, but always calculates as "prior", which fortunately is the default
> > value for this property.
> > 
> > But especially "averaged-abscissa" is much better than "prior". 
> 
> Should "averaged-abscissa" be the default for new documents?

That is not possible, default "prior" is set in ODF. And for preselect in the dialog, I would prefer, that the method the user has used last, keeps preselected, when he opens the dialog again. Excel does not have "averaged-abscissa", so I think we need to teach the user about it. But that is a documentation and marketing task.
Comment 3 Samuel Mehrbrodt (allotropia) 2021-02-09 12:04:20 UTC
(In reply to Regina Henschel from comment #2)

> That is not possible, default "prior" is set in ODF.

Well the UI defaults do not necessarily have to match file format defaults.

> And for preselect in
> the dialog, I would prefer, that the method the user has used last, keeps
> preselected, when he opens the dialog again.

Valid point, will consider this.

> Excel does not have
> "averaged-abscissa", so I think we need to teach the user about it.

So there is nothing we can map this to in OOXML export?
Comment 4 Regina Henschel 2021-02-09 13:30:00 UTC
The possible types in OOXML are defined in §21.2.3.50 ST_TrendlineType. That are exp, linear, log, movingAvg, poly, power. It does not specify how 'movingAvg' has to be calculated. The UI of Excel has no option to specify how to calculate it. So I think, Excel is not able to use "averaged-abscissa".

Because it is only available for Linux, I cannot test Gnumeric. But you might look what Gnumeric produces, when it writes an xlsx file from a chart, which uses "averaged-abscissa". You need a Gnumeric file for import test anyway.
Comment 5 Samuel Mehrbrodt (allotropia) 2021-02-10 07:50:08 UTC
(In reply to Regina Henschel from comment #4)
> The possible types in OOXML are defined in §21.2.3.50 ST_TrendlineType. That
> are exp, linear, log, movingAvg, poly, power. It does not specify how
> 'movingAvg' has to be calculated. The UI of Excel has no option to specify
> how to calculate it. So I think, Excel is not able to use
> "averaged-abscissa".

Ok, thx for checking. Then maybe we should leave the default as is.
> 
> Because it is only available for Linux, I cannot test Gnumeric. But you
> might look what Gnumeric produces, when it writes an xlsx file from a chart,
> which uses "averaged-abscissa". You need a Gnumeric file for import test
> anyway.

Ok.

Can you tell which formulas to use for "averaged abscissa" and "center" (also the formula used for "prior" for comparison)?
Our chart engine does not support these types yet, so I have to add them.
Comment 6 Regina Henschel 2021-02-10 11:27:09 UTC
(In reply to Samuel Mehrbrodt (allotropia) from comment #5)
> Can you tell which formulas to use for "averaged abscissa" and "center"
> (also the formula used for "prior" for comparison)?
> Our chart engine does not support these types yet, so I have to add them.

The ODF specification is more precise than OOXML in such things. You find the formulas in part 3, section 20.47 chart:regression-moving-type.
The ODF 1.3 specification is available in several formats from
http://docs.oasis-open.org/office/OpenDocument/v1.3/
Comment 7 Regina Henschel 2021-02-10 13:26:47 UTC
Created attachment 169648 [details]
Manually calculate the regression curves

The file contains a manual calculation of the three types of regression curves. Hopefully this helps for understanding the formulas.
Comment 8 Samuel Mehrbrodt (allotropia) 2021-02-10 14:50:19 UTC
(In reply to Regina Henschel from comment #6)
> You find
> the formulas in part 3, section 20.47 chart:regression-moving-type.
> The ODF 1.3 specification is available in several formats from
> http://docs.oasis-open.org/office/OpenDocument/v1.3/

Thx for the hint, for reference, I paste the full link here: https://docs.oasis-open.org/office/OpenDocument/v1.3/cs02/part3-schema/OpenDocument-v1.3-cs02-part3-schema.html#property-chart_regression-moving-type
Comment 9 Commit Notification 2021-02-22 06:36:52 UTC
Samuel Mehrbrodt committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/4141533a3fb984fbaefe87b15fceeda7f2082061

[API CHANGE] tdf#133423 Implement chart:regression-moving-type

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2021-02-22 06:39:04 UTC
Samuel Mehrbrodt committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/33a193130f7643f5bab2c9369f5d700114c6fbee

tdf#133423 Add ODF import/export test

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 11 Commit Notification 2021-02-22 06:40:16 UTC
Samuel Mehrbrodt committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/e4037a34f784cc54fba08bdac48f1660f7c2ec5f

tdf#133423 Add UITest

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 12 Buovjaga 2021-02-23 14:01:01 UTC
Verified by creating a new XY scatter chart from the data in attachment 161310 [details], selecting data series, right-clicking and inserting trend line

Arch Linux 64-bit
Version: 7.2.0.0.alpha0+ / LibreOffice Community
Build ID: dec32864a61104001cb6ff63c218ea5fb2a7ab1e
CPU threads: 8; OS: Linux 5.10; UI render: default; VCL: kf5
Locale: fi-FI (fi_FI.UTF-8); UI: en-US
Calc: threaded
Built on 22 February 2021
Comment 13 Regina Henschel 2021-03-06 22:29:26 UTC
Created attachment 170278 [details]
Compare manually calculated moving average with chart feature

The trend line of type "Central" is wrong. It looks as if the trend line is drawn on the data points (x|y) instead of the trend line point (r|s).

types "Prior" and "Averaged Abscissa" are OK.
Comment 14 Regina Henschel 2021-03-06 22:30:08 UTC
See attached file. "Central" is wrong.
Comment 15 Commit Notification 2021-03-10 07:39:00 UTC
Samuel Mehrbrodt committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/a9b71098845edcb4f6b4629795168d174f28ef70

tdf#133423 Fix 'central' formula for moving average

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 16 Samuel Mehrbrodt (allotropia) 2021-03-10 07:42:46 UTC
(In reply to Regina Henschel from comment #13)
> Created attachment 170278 [details]
> Compare manually calculated moving average with chart feature
> 
> The trend line of type "Central" is wrong. It looks as if the trend line is
> drawn on the data points (x|y) instead of the trend line point (r|s).

Thanks for the helpful examples, I (hopefully) fixed that with above commit.
Comment 17 Regina Henschel 2021-03-23 11:13:57 UTC
It is correct now in Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: 076da4cd9d61adf15942a7bfc350bd0d268f64f1
CPU threads: 8; OS: Windows 10.0 Build 19041; UI render: Skia/Raster; VCL: win
Locale: de-DE (en_US); UI: en-US
Calc: CL
Comment 18 stragu 2021-06-04 14:57:54 UTC
FYI, I added a screenshot to the release notes: https://wiki.documentfoundation.org/ReleaseNotes/7.2#Chart