Bug 94635 - Add FORECAST functions using Triple Exponential Smoothing
Summary: Add FORECAST functions using Triple Exponential Smoothing
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Winfried Donkers
URL:
Whiteboard: target:5.2.0 target:5.3.0 target:5.2.4
Keywords:
Depends on:
Blocks: 97021
  Show dependency treegraph
 
Reported: 2015-09-30 08:34 UTC by Winfried Donkers
Modified: 2016-10-15 00:58 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
some intermediate results (67.84 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-12-10 10:18 UTC, Winfried Donkers
Details
some intermediate results (93.51 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-12-18 11:22 UTC, Winfried Donkers
Details
some results and comparisons (162.53 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-01-06 15:29 UTC, Winfried Donkers
Details
some examples and comparisons (162.65 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-09-15 11:12 UTC, Winfried Donkers
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Winfried Donkers 2015-09-30 08:34:25 UTC
With Excel 2016, 5 new functions have been added:
FORECAST.LINEAR
FORECAST.ETS
FORECAST.ETS.CONFINT
FORECAST.ETS.SEASONALITY
FORECAST.ETS.STAT
(and the function FORECAST has been labeled obsolete, but is still supported)

see: https://support.office.com/en-us/article/Forecasting-functions-897a2fe9-6595-4680-a0b0-93e0308d5f6e?ui=en-US&rs=en-US&ad=US#_forecast.ets
Comment 1 Winfried Donkers 2015-12-07 11:21:21 UTC
Further studies of publications and literature about Triple Exponential Smoothing (AKA Holt-Winters method) shows that there are 2 methods, depending on the seasonal influences.
If the seasonal influence is absolute (e.g. in July 10 extra icecreams are sold), the additive method is used, if the seasonal influence is relative (e.g. in July 10% extra icecreams are sold), the multiplicative method is used.

For the additive there is a generally accepted method to calculate prediction intervals, often -but incorrectly- called confidence intervals. As the interval is calculated for predicted values, not for actual observations, it is not possible to calculate a proper confidence interval.
For the multiplicative method I haven't found a method that is recommended yet.

Excel 2016 uses the additive method.
I intend to add both methods to Calc.
Comment 2 Winfried Donkers 2015-12-10 10:18:19 UTC
Created attachment 121190 [details]
some intermediate results

Calc document contains 4 datasets with observations, 3 taken from forecasting examples and 1 (the sinus) to modify various parameters of the observations for testing.

Each dataset has 3 ETS-forecasts: Calc additive, Calc multiplicative and Excel2016 additive, as well as the statistics for these 3 forecasts. The RMSE (Root Mean Squared Error) is used for optimising the forecast.

The prediction interval only shows for Excel2016, as I have not yet implemented this in Calc.
Comment 3 Winfried Donkers 2015-12-18 11:22:49 UTC
Created attachment 121384 [details]
some intermediate results

Now with prediction interval calculations.
Comment 4 Winfried Donkers 2016-01-06 15:29:12 UTC
Created attachment 121750 [details]
some results and comparisons

Finally, the code is complete. Now retest every aspect and behaviour and add a unittest...
Comment 5 Commit Notification 2016-03-03 16:31:25 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f336f63da900d76c2bf6e5690f1c8a7bd15a0aa2

tdf#94635 Add FORECAST.ETS functions to Calc

It will be available in 5.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 6 Gerry 2016-03-04 08:33:08 UTC
Winfried, thanks a lot!
Comment 7 Winfried Donkers 2016-03-04 08:45:45 UTC
(In reply to Gerry from comment #6)
> Winfried, thanks a lot!

You're welcome.
I just think I haven't updated you wiki with Calc-Excel comparisons. (Note that  Excel uses slightly different function names because Excel only supports 1 method (additive) whereas Calc supports both additive and multiplicative methods with triple exponential smoothing. Also, The Excel FORECAST.ETS.SEASONALITY is not really necessary as a separate function in Calc (though it is available) because this value can be returned from the FORECAST.ETS.STAT.ADD/MULT functions in Calc.)
Comment 8 Commit Notification 2016-03-04 17:52:59 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c788f63726df3340e787bb92477d7ad31e7bc952

follow up of tdf#94635

It will be available in 5.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 9 Commit Notification 2016-03-08 12:05:39 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=08306201bc88b2efe915e27799b44aaf00a3db7e

tdf#94635 synchronise argument labels in function wizard with

It will be available in 5.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2016-03-14 14:48:24 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0aa29f32f9c39f598f43cdf3664b89d6cb151b17

prefix domain namespace to FORECAST.* functions for ODFF, tdf#94635 follow-up

It will be available in 5.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 11 Winfried Donkers 2016-03-15 16:33:44 UTC
(In reply to Commit Notification from comment #10)
> Eike Rathke committed a patch related to this issue.
> prefix domain namespace to FORECAST.* functions for ODFF, tdf#94635 follow-up

@Eike: I noticed the problem last Sunday whilst opening an xlsx-document but you were to fast to let me fix it ;-)
Thanks!
Comment 12 Winfried Donkers 2016-09-15 11:12:04 UTC
Created attachment 127341 [details]
some examples and comparisons

Updated document reflecting current function names.
Comment 13 Commit Notification 2016-10-13 18:57:18 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=ad707a3dafd020949cfd94098dc51c69466dafb8

tdf#94635 follow up; correct handling of double x-values in case of

It will be available in 5.3.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 14 Commit Notification 2016-10-13 19:08:32 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=fb790880eb11552b2e7d1dcf6c09d2663712290f

tdf#94635 follow up; handle linear data properly when samples in period

It will be available in 5.3.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 15 Eike Rathke 2016-10-13 19:20:24 UTC
@Winfried:
Should we cherry-pick/backport the last two patches to the 5-2 branch?
Comment 16 Winfried Donkers 2016-10-14 05:49:10 UTC
(In reply to Eike Rathke from comment #15)
> @Winfried:
> Should we cherry-pick/backport the last two patches to the 5-2 branch?

@Eike:
Yes we should, they fix bugs and are not enhancements.
Comment 17 Commit Notification 2016-10-14 18:10:02 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f617d9466632f656811474cd49311e13ab950753&h=libreoffice-5-2

tdf#94635 follow up; correct handling of double x-values in case of

It will be available in 5.2.4.

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

Affected users are encouraged to test the fix and report feedback.
Comment 18 Commit Notification 2016-10-15 00:58:44 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=aaa38fd34faab3108245f8fa8c13642919ff0c95&h=libreoffice-5-2

tdf#94635 follow up; handle linear data properly when samples in period

It will be available in 5.2.4.

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

Affected users are encouraged to test the fix and report feedback.