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.
(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?
(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.
(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?
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.
(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.
(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/
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.
(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
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.
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.
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.
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
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.
See attached file. "Central" is wrong.
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.
(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.
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