LibreOffice Module sc (master)  1
interpr8.cxx
Go to the documentation of this file.
1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
2 /*
3  * This file is part of the LibreOffice project.
4  *
5  * This Source Code Form is subject to the terms of the Mozilla Public
6  * License, v. 2.0. If a copy of the MPL was not distributed with this
7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
8  *
9  */
10 
11 #include <interpre.hxx>
12 #include <cellvalue.hxx>
13 #include <scmatrix.hxx>
14 #include <comphelper/random.hxx>
15 #include <formula/token.hxx>
16 #include <sal/log.hxx>
17 #include <svl/numformat.hxx>
18 
19 #include <cmath>
20 #include <memory>
21 #include <vector>
22 
23 using namespace formula;
24 
25 namespace {
26 
27 struct DataPoint
28 {
29  double X, Y;
30 
31  DataPoint( double rX, double rY ) : X( rX ), Y( rY ) {};
32 };
33 
34 }
35 
36 static bool lcl_SortByX( const DataPoint &lhs, const DataPoint &rhs ) { return lhs.X < rhs.X; }
37 
38 /*
39  * ScETSForecastCalculation
40  *
41  * Class is set up to be used with Calc's FORECAST.ETS
42  * functions and with chart extrapolations (not yet implemented).
43  *
44  * Triple Exponential Smoothing (Holt-Winters method)
45  *
46  * Forecasting of a linear change in data over time (y=a+b*x) with
47  * superimposed absolute or relative seasonal deviations, using additive
48  * respectively multiplicative Holt-Winters method.
49  *
50  * Initialisation and forecasting calculations are based on
51  * Engineering Statistics Handbook, 6.4.3.5 Triple Exponential Smoothing
52  * see "http://www.itl.nist.gov/div898/handbook/pmc/section4/pmc435.htm"
53  * Further to the above is that initial calculation of Seasonal effect
54  * is corrected for trend.
55  *
56  * Prediction Interval calculations are based on
57  * Yar & Chatfield, Prediction Intervals for the Holt-Winters forecasting
58  * procedure, International Journal of Forecasting, 1990, Vol.6, pp127-137
59  * The calculation here is a simplified numerical approximation of the above,
60  * using random distributions.
61  *
62  * Double Exponential Smoothing (Holt-Winters method)
63  *
64  * Forecasting of a linear change in data over time (y=a+b*x), using
65  * the Holt-Winters method.
66  *
67  * Initialisation and forecasting calculations are based on
68  * Engineering Statistics Handbook, 6.4.3.3 Double Exponential Smoothing
69  * see "http://www.itl.nist.gov/div898/handbook/pmc/section4/pmc433.htm"
70  *
71  * Prediction Interval calculations are based on
72  * Statistical Methods for Forecasting, Bovas & Ledolter, 2009, 3.8 Prediction
73  * Intervals for Future Values
74  *
75  */
76 
77 namespace {
78 
79 class ScETSForecastCalculation
80 {
81 private:
82  SvNumberFormatter* mpFormatter;
83  std::vector< DataPoint > maRange; // data (X, Y)
84  std::unique_ptr<double[]> mpBase; // calculated base value array
85  std::unique_ptr<double[]> mpTrend; // calculated trend factor array
86  std::unique_ptr<double[]> mpPerIdx; // calculated periodical deviation array, not used with eds
87  std::unique_ptr<double[]> mpForecast; // forecasted value array
88  SCSIZE mnSmplInPrd; // samples per period
89  double mfStepSize; // increment of X in maRange
90  double mfAlpha, mfBeta, mfGamma; // constants to minimize the RMSE in the ES-equations
91  SCSIZE mnCount; // No of data points
92  bool mbInitialised;
93  int mnMonthDay; // n-month X-interval, value is day of month
94  // accuracy indicators
95  double mfMAE; // mean absolute error
96  double mfMASE; // mean absolute scaled error
97  double mfMSE; // mean squared error (variation)
98  double mfRMSE; // root mean squared error (standard deviation)
99  double mfSMAPE; // symmetric mean absolute error
100  FormulaError mnErrorValue;
101  bool bAdditive; // true: additive method, false: multiplicative method
102  bool bEDS; // true: EDS, false: ETS
103 
104  // constants used in determining best fit for alpha, beta, gamma
105  static constexpr double cfMinABCResolution = 0.001; // minimum change of alpha, beta, gamma
106  static const SCSIZE cnScenarios = 1000; // No. of scenarios to calculate for PI calculations
107 
108  bool initData();
109  void prefillBaseData();
110  bool prefillTrendData();
111  bool prefillPerIdx();
112  void initCalc();
113  void refill();
114  SCSIZE CalcPeriodLen();
115  void CalcAlphaBetaGamma();
116  void CalcBetaGamma();
117  void CalcGamma();
118  void calcAccuracyIndicators();
119  void GetForecast( double fTarget, double& rForecast );
120  double RandDev();
121  double convertXtoMonths( double x );
122 
123 public:
124  ScETSForecastCalculation( SCSIZE nSize, SvNumberFormatter* pFormatter );
125 
126  bool PreprocessDataRange( const ScMatrixRef& rMatX, const ScMatrixRef& rMatY, int nSmplInPrd,
127  bool bDataCompletion, int nAggregation, const ScMatrixRef& rTMat,
128  ScETSType eETSType );
129  FormulaError GetError() const { return mnErrorValue; };
130  void GetForecastRange( const ScMatrixRef& rTMat, const ScMatrixRef& rFcMat );
131  void GetStatisticValue( const ScMatrixRef& rTypeMat, const ScMatrixRef& rStatMat );
132  void GetSamplesInPeriod( double& rVal );
133  void GetEDSPredictionIntervals( const ScMatrixRef& rTMat, const ScMatrixRef& rPIMat, double fPILevel );
134  void GetETSPredictionIntervals( const ScMatrixRef& rTMat, const ScMatrixRef& rPIMat, double fPILevel );
135 };
136 
137 }
138 
139 ScETSForecastCalculation::ScETSForecastCalculation( SCSIZE nSize, SvNumberFormatter* pFormatter )
140  : mpFormatter(pFormatter)
141  , mnSmplInPrd(0)
142  , mfStepSize(0.0)
143  , mfAlpha(0.0)
144  , mfBeta(0.0)
145  , mfGamma(0.0)
146  , mnCount(nSize)
147  , mbInitialised(false)
148  , mnMonthDay(0)
149  , mfMAE(0.0)
150  , mfMASE(0.0)
151  , mfMSE(0.0)
152  , mfRMSE(0.0)
153  , mfSMAPE(0.0)
154  , mnErrorValue(FormulaError::NONE)
155  , bAdditive(false)
156  , bEDS(false)
157 {
158  maRange.reserve( mnCount );
159 }
160 
161 bool ScETSForecastCalculation::PreprocessDataRange( const ScMatrixRef& rMatX, const ScMatrixRef& rMatY, int nSmplInPrd,
162  bool bDataCompletion, int nAggregation, const ScMatrixRef& rTMat,
163  ScETSType eETSType )
164 {
165  bEDS = ( nSmplInPrd == 0 );
166  bAdditive = ( eETSType == etsAdd || eETSType == etsPIAdd || eETSType == etsStatAdd );
167 
168  // maRange needs to be sorted by X
169  for ( SCSIZE i = 0; i < mnCount; i++ )
170  maRange.emplace_back( rMatX->GetDouble( i ), rMatY->GetDouble( i ) );
171  sort( maRange.begin(), maRange.end(), lcl_SortByX );
172 
173  if ( rTMat )
174  {
175  if ( eETSType != etsPIAdd && eETSType != etsPIMult )
176  {
177  if ( rTMat->GetDouble( 0 ) < maRange[ 0 ].X )
178  {
179  // target cannot be less than start of X-range
180  mnErrorValue = FormulaError::IllegalFPOperation;
181  return false;
182  }
183  }
184  else
185  {
186  if ( rTMat->GetDouble( 0 ) < maRange[ mnCount - 1 ].X )
187  {
188  // target cannot be before end of X-range
189  mnErrorValue = FormulaError::IllegalFPOperation;
190  return false;
191  }
192  }
193  }
194 
195  // Month intervals don't have exact stepsize, so first
196  // detect if month interval is used.
197  // Method: assume there is an month interval and verify.
198  // If month interval is used, replace maRange.X with month values
199  // for ease of calculations.
200  Date aNullDate = mpFormatter->GetNullDate();
201  Date aDate = aNullDate + static_cast< sal_Int32 >( maRange[ 0 ].X );
202  mnMonthDay = aDate.GetDay();
203  for ( SCSIZE i = 1; i < mnCount && mnMonthDay; i++ )
204  {
205  Date aDate1 = aNullDate + static_cast< sal_Int32 >( maRange[ i ].X );
206  if ( aDate != aDate1 )
207  {
208  if ( aDate1.GetDay() != mnMonthDay )
209  mnMonthDay = 0;
210  }
211  }
212 
213  mfStepSize = ::std::numeric_limits<double>::max();
214  if ( mnMonthDay )
215  {
216  for ( SCSIZE i = 0; i < mnCount; i++ )
217  {
218  aDate = aNullDate + static_cast< sal_Int32 >( maRange[ i ].X );
219  maRange[ i ].X = aDate.GetYear() * 12 + aDate.GetMonth();
220  }
221  }
222  for ( SCSIZE i = 1; i < mnCount; i++ )
223  {
224  double fStep = maRange[ i ].X - maRange[ i - 1 ].X;
225  if ( fStep == 0.0 )
226  {
227  if ( nAggregation == 0 )
228  {
229  // identical X-values are not allowed
230  mnErrorValue = FormulaError::NoValue;
231  return false;
232  }
233  double fTmp = maRange[ i - 1 ].Y;
234  SCSIZE nCounter = 1;
235  switch ( nAggregation )
236  {
237  case 1 : // AVERAGE (default)
238  while ( i < mnCount && maRange[ i ].X == maRange[ i - 1 ].X )
239  {
240  maRange.erase( maRange.begin() + i );
241  --mnCount;
242  }
243  break;
244  case 7 : // SUM
245  while ( i < mnCount && maRange[ i ].X == maRange[ i - 1 ].X )
246  {
247  fTmp += maRange[ i ].Y;
248  maRange.erase( maRange.begin() + i );
249  --mnCount;
250  }
251  maRange[ i - 1 ].Y = fTmp;
252  break;
253 
254  case 2 : // COUNT
255  case 3 : // COUNTA (same as COUNT as there are no non-numeric Y-values)
256  while ( i < mnCount && maRange[ i ].X == maRange[ i - 1 ].X )
257  {
258  nCounter++;
259  maRange.erase( maRange.begin() + i );
260  --mnCount;
261  }
262  maRange[ i - 1 ].Y = nCounter;
263  break;
264 
265  case 4 : // MAX
266  while ( i < mnCount && maRange[ i ].X == maRange[ i - 1 ].X )
267  {
268  if ( maRange[ i ].Y > fTmp )
269  fTmp = maRange[ i ].Y;
270  maRange.erase( maRange.begin() + i );
271  --mnCount;
272  }
273  maRange[ i - 1 ].Y = fTmp;
274  break;
275 
276  case 5 : // MEDIAN
277  {
278  std::vector< double > aTmp { maRange[ i - 1 ].Y };
279  while ( i < mnCount && maRange[ i ].X == maRange[ i - 1 ].X )
280  {
281  aTmp.push_back( maRange[ i ].Y );
282  nCounter++;
283  maRange.erase( maRange.begin() + i );
284  --mnCount;
285  }
286  sort( aTmp.begin(), aTmp.end() );
287 
288  if ( nCounter % 2 )
289  maRange[ i - 1 ].Y = aTmp[ nCounter / 2 ];
290  else
291  maRange[ i - 1 ].Y = ( aTmp[ nCounter / 2 ] + aTmp[ nCounter / 2 - 1 ] ) / 2.0;
292  }
293  break;
294 
295  case 6 : // MIN
296  while ( i < mnCount && maRange[ i ].X == maRange[ i - 1 ].X )
297  {
298  if ( maRange[ i ].Y < fTmp )
299  fTmp = maRange[ i ].Y;
300  maRange.erase( maRange.begin() + i );
301  --mnCount;
302  }
303  maRange[ i - 1 ].Y = fTmp;
304  break;
305  }
306  if ( i < mnCount - 1 )
307  fStep = maRange[ i ].X - maRange[ i - 1 ].X;
308  else
309  fStep = mfStepSize;
310  }
311  if ( fStep > 0 && fStep < mfStepSize )
312  mfStepSize = fStep;
313  }
314 
315  // step must be constant (or gap multiple of step)
316  bool bHasGap = false;
317  for ( SCSIZE i = 1; i < mnCount && !bHasGap; i++ )
318  {
319  double fStep = maRange[ i ].X - maRange[ i - 1 ].X;
320 
321  if ( fStep != mfStepSize )
322  {
323  if ( fmod( fStep, mfStepSize ) != 0.0 )
324  {
325  // step not constant nor multiple of mfStepSize in case of gaps
326  mnErrorValue = FormulaError::NoValue;
327  return false;
328  }
329  bHasGap = true;
330  }
331  }
332 
333  // fill gaps with values depending on bDataCompletion
334  if ( bHasGap )
335  {
336  SCSIZE nMissingXCount = 0;
337  double fOriginalCount = static_cast< double >( mnCount );
338  if ( mnMonthDay )
339  aDate = aNullDate + static_cast< sal_Int32 >( maRange[ 0 ].X );
340  for ( SCSIZE i = 1; i < mnCount; i++ )
341  {
342  double fDist;
343  if ( mnMonthDay )
344  {
345  Date aDate1 = aNullDate + static_cast< sal_Int32 >( maRange[ i ].X );
346  fDist = 12 * ( aDate1.GetYear() - aDate.GetYear() ) +
347  ( aDate1.GetMonth() - aDate.GetMonth() );
348  aDate = aDate1;
349  }
350  else
351  fDist = maRange[ i ].X - maRange[ i - 1 ].X;
352  if ( fDist > mfStepSize )
353  {
354  // gap, insert missing data points
355  double fYGap = ( maRange[ i ].Y + maRange[ i - 1 ].Y ) / 2.0;
356  for ( KahanSum fXGap = maRange[ i - 1].X + mfStepSize; fXGap < maRange[ i ].X; fXGap += mfStepSize )
357  {
358  maRange.insert( maRange.begin() + i, DataPoint( fXGap.get(), ( bDataCompletion ? fYGap : 0.0 ) ) );
359  i++;
360  mnCount++;
361  nMissingXCount++;
362  if ( static_cast< double >( nMissingXCount ) / fOriginalCount > 0.3 )
363  {
364  // maximum of 30% missing points exceeded
365  mnErrorValue = FormulaError::NoValue;
366  return false;
367  }
368  }
369  }
370  }
371  }
372 
373  if ( nSmplInPrd != 1 )
374  mnSmplInPrd = nSmplInPrd;
375  else
376  {
377  mnSmplInPrd = CalcPeriodLen();
378  if ( mnSmplInPrd == 1 )
379  bEDS = true; // period length 1 means no periodic data: EDS suffices
380  }
381 
382  if ( !initData() )
383  return false; // note: mnErrorValue is set in called function(s)
384 
385  return true;
386 }
387 
388 bool ScETSForecastCalculation::initData( )
389 {
390  // give various vectors size and initial value
391  mpBase.reset( new double[ mnCount ] );
392  mpTrend.reset( new double[ mnCount ] );
393  if ( !bEDS )
394  mpPerIdx.reset( new double[ mnCount ] );
395  mpForecast.reset( new double[ mnCount ] );
396  mpForecast[ 0 ] = maRange[ 0 ].Y;
397 
398  if ( prefillTrendData() )
399  {
400  if ( prefillPerIdx() )
401  {
402  prefillBaseData();
403  return true;
404  }
405  }
406  return false;
407 }
408 
409 bool ScETSForecastCalculation::prefillTrendData()
410 {
411  if ( bEDS )
412  mpTrend[ 0 ] = ( maRange[ mnCount - 1 ].Y - maRange[ 0 ].Y ) / static_cast< double >( mnCount - 1 );
413  else
414  {
415  // we need at least 2 periods in the data range
416  if ( mnCount < 2 * mnSmplInPrd )
417  {
418  mnErrorValue = FormulaError::NoValue;
419  return false;
420  }
421 
422  KahanSum fSum = 0.0;
423  for ( SCSIZE i = 0; i < mnSmplInPrd; i++ )
424  {
425  fSum += maRange[ i + mnSmplInPrd ].Y;
426  fSum -= maRange[ i ].Y;
427  }
428  double fTrend = fSum.get() / static_cast< double >( mnSmplInPrd * mnSmplInPrd );
429 
430  mpTrend[ 0 ] = fTrend;
431  }
432 
433  return true;
434 }
435 
436 bool ScETSForecastCalculation::prefillPerIdx()
437 {
438  if ( !bEDS )
439  {
440  // use as many complete periods as available
441  if ( mnSmplInPrd == 0 )
442  {
443  // should never happen; if mnSmplInPrd equals 0, bEDS is true
444  mnErrorValue = FormulaError::UnknownState;
445  return false;
446  }
447  SCSIZE nPeriods = mnCount / mnSmplInPrd;
448  std::vector< KahanSum > aPeriodAverage( nPeriods, 0.0 );
449  for ( SCSIZE i = 0; i < nPeriods ; i++ )
450  {
451  for ( SCSIZE j = 0; j < mnSmplInPrd; j++ )
452  aPeriodAverage[ i ] += maRange[ i * mnSmplInPrd + j ].Y;
453  aPeriodAverage[ i ] /= static_cast< double >( mnSmplInPrd );
454  if ( aPeriodAverage[ i ] == 0.0 )
455  {
456  SAL_WARN( "sc.core", "prefillPerIdx(), average of 0 will cause divide by zero error, quitting calculation" );
457  mnErrorValue = FormulaError::DivisionByZero;
458  return false;
459  }
460  }
461 
462  for ( SCSIZE j = 0; j < mnSmplInPrd; j++ )
463  {
464  KahanSum fI = 0.0;
465  for ( SCSIZE i = 0; i < nPeriods ; i++ )
466  {
467  // adjust average value for position within period
468  if ( bAdditive )
469  fI += maRange[ i * mnSmplInPrd + j ].Y -
470  ( aPeriodAverage[ i ].get() + ( static_cast< double >( j ) - 0.5 * ( mnSmplInPrd - 1 ) ) *
471  mpTrend[ 0 ] );
472  else
473  fI += maRange[ i * mnSmplInPrd + j ].Y /
474  ( aPeriodAverage[ i ].get() + ( static_cast< double >( j ) - 0.5 * ( mnSmplInPrd - 1 ) ) *
475  mpTrend[ 0 ] );
476  }
477  mpPerIdx[ j ] = fI.get() / nPeriods;
478  }
479  if (mnSmplInPrd < mnCount)
480  mpPerIdx[mnSmplInPrd] = 0.0;
481  }
482  return true;
483 }
484 
485 void ScETSForecastCalculation::prefillBaseData()
486 {
487  if ( bEDS )
488  mpBase[ 0 ] = maRange[ 0 ].Y;
489  else
490  mpBase[ 0 ] = maRange[ 0 ].Y / mpPerIdx[ 0 ];
491 }
492 
493 void ScETSForecastCalculation::initCalc()
494 {
495  if ( !mbInitialised )
496  {
497  CalcAlphaBetaGamma();
498 
499  mbInitialised = true;
500  calcAccuracyIndicators();
501  }
502 }
503 
504 void ScETSForecastCalculation::calcAccuracyIndicators()
505 {
506  KahanSum fSumAbsErr = 0.0;
507  KahanSum fSumDivisor = 0.0;
508  KahanSum fSumErrSq = 0.0;
509  KahanSum fSumAbsPercErr = 0.0;
510 
511  for ( SCSIZE i = 1; i < mnCount; i++ )
512  {
513  double fError = mpForecast[ i ] - maRange[ i ].Y;
514  fSumAbsErr += fabs( fError );
515  fSumErrSq += fError * fError;
516  fSumAbsPercErr += fabs( fError ) / ( fabs( mpForecast[ i ] ) + fabs( maRange[ i ].Y ) );
517  }
518 
519  for ( SCSIZE i = 2; i < mnCount; i++ )
520  fSumDivisor += fabs( maRange[ i ].Y - maRange[ i - 1 ].Y );
521 
522  int nCalcCount = mnCount - 1;
523  mfMAE = fSumAbsErr.get() / nCalcCount;
524  mfMASE = fSumAbsErr.get() / ( nCalcCount * fSumDivisor.get() / ( nCalcCount - 1 ) );
525  mfMSE = fSumErrSq.get() / nCalcCount;
526  mfRMSE = sqrt( mfMSE );
527  mfSMAPE = fSumAbsPercErr.get() * 2.0 / nCalcCount;
528 }
529 
530 /*
531  * CalcPeriodLen() calculates the most likely length of a period.
532  *
533  * Method used: for all possible values (between mnCount/2 and 2) compare for
534  * each (sample-previous sample) with next period and calculate mean error.
535  * Use as much samples as possible for each period length and the most recent samples
536  * Return the period length with the lowest mean error.
537  */
538 SCSIZE ScETSForecastCalculation::CalcPeriodLen()
539 {
540  SCSIZE nBestVal = mnCount;
541  double fBestME = ::std::numeric_limits<double>::max();
542 
543  for ( SCSIZE nPeriodLen = mnCount / 2; nPeriodLen >= 1; nPeriodLen-- )
544  {
545  KahanSum fMeanError = 0.0;
546  SCSIZE nPeriods = mnCount / nPeriodLen;
547  SCSIZE nStart = mnCount - ( nPeriods * nPeriodLen ) + 1;
548  for ( SCSIZE i = nStart; i < ( mnCount - nPeriodLen ); i++ )
549  {
550  fMeanError += fabs( ( maRange[ i ].Y - maRange[ i - 1 ].Y ) -
551  ( maRange[ nPeriodLen + i ].Y - maRange[ nPeriodLen + i - 1 ].Y ) );
552  }
553  double fMeanErrorGet = fMeanError.get();
554  fMeanErrorGet /= static_cast< double >( ( nPeriods - 1 ) * nPeriodLen - 1 );
555 
556  if ( fMeanErrorGet <= fBestME || fMeanErrorGet == 0.0 )
557  {
558  nBestVal = nPeriodLen;
559  fBestME = fMeanErrorGet;
560  }
561  }
562  return nBestVal;
563 }
564 
565 void ScETSForecastCalculation::CalcAlphaBetaGamma()
566 {
567  double f0 = 0.0;
568  mfAlpha = f0;
569  if ( bEDS )
570  {
571  mfBeta = 0.0; // beta is not used with EDS
572  CalcGamma();
573  }
574  else
575  CalcBetaGamma();
576  refill();
577  double fE0 = mfMSE;
578 
579  double f2 = 1.0;
580  mfAlpha = f2;
581  if ( bEDS )
582  CalcGamma();
583  else
584  CalcBetaGamma();
585  refill();
586  double fE2 = mfMSE;
587 
588  double f1 = 0.5;
589  mfAlpha = f1;
590  if ( bEDS )
591  CalcGamma();
592  else
593  CalcBetaGamma();
594  refill();
595 
596  if ( fE0 == mfMSE && mfMSE == fE2 )
597  {
598  mfAlpha = 0;
599  if ( bEDS )
600  CalcGamma();
601  else
602  CalcBetaGamma();
603  refill();
604  return;
605  }
606  while ( ( f2 - f1 ) > cfMinABCResolution )
607  {
608  if ( fE2 > fE0 )
609  {
610  f2 = f1;
611  fE2 = mfMSE;
612  f1 = ( f0 + f1 ) / 2;
613  }
614  else
615  {
616  f0 = f1;
617  fE0 = mfMSE;
618  f1 = ( f1 + f2 ) / 2;
619  }
620  mfAlpha = f1;
621  if ( bEDS )
622  CalcGamma();
623  else
624  CalcBetaGamma();
625  refill();
626  }
627  if ( fE2 > fE0 )
628  {
629  if ( fE0 < mfMSE )
630  {
631  mfAlpha = f0;
632  if ( bEDS )
633  CalcGamma();
634  else
635  CalcBetaGamma();
636  refill();
637  }
638  }
639  else
640  {
641  if ( fE2 < mfMSE )
642  {
643  mfAlpha = f2;
644  if ( bEDS )
645  CalcGamma();
646  else
647  CalcBetaGamma();
648  refill();
649  }
650  }
651  calcAccuracyIndicators();
652 }
653 
654 void ScETSForecastCalculation::CalcBetaGamma()
655 {
656  double f0 = 0.0;
657  mfBeta = f0;
658  CalcGamma();
659  refill();
660  double fE0 = mfMSE;
661 
662  double f2 = 1.0;
663  mfBeta = f2;
664  CalcGamma();
665  refill();
666  double fE2 = mfMSE;
667 
668  double f1 = 0.5;
669  mfBeta = f1;
670  CalcGamma();
671  refill();
672 
673  if ( fE0 == mfMSE && mfMSE == fE2 )
674  {
675  mfBeta = 0;
676  CalcGamma();
677  refill();
678  return;
679  }
680  while ( ( f2 - f1 ) > cfMinABCResolution )
681  {
682  if ( fE2 > fE0 )
683  {
684  f2 = f1;
685  fE2 = mfMSE;
686  f1 = ( f0 + f1 ) / 2;
687  }
688  else
689  {
690  f0 = f1;
691  fE0 = mfMSE;
692  f1 = ( f1 + f2 ) / 2;
693  }
694  mfBeta = f1;
695  CalcGamma();
696  refill();
697  }
698  if ( fE2 > fE0 )
699  {
700  if ( fE0 < mfMSE )
701  {
702  mfBeta = f0;
703  CalcGamma();
704  refill();
705  }
706  }
707  else
708  {
709  if ( fE2 < mfMSE )
710  {
711  mfBeta = f2;
712  CalcGamma();
713  refill();
714  }
715  }
716 }
717 
718 void ScETSForecastCalculation::CalcGamma()
719 {
720  double f0 = 0.0;
721  mfGamma = f0;
722  refill();
723  double fE0 = mfMSE;
724 
725  double f2 = 1.0;
726  mfGamma = f2;
727  refill();
728  double fE2 = mfMSE;
729 
730  double f1 = 0.5;
731  mfGamma = f1;
732  refill();
733 
734  if ( fE0 == mfMSE && mfMSE == fE2 )
735  {
736  mfGamma = 0;
737  refill();
738  return;
739  }
740  while ( ( f2 - f1 ) > cfMinABCResolution )
741  {
742  if ( fE2 > fE0 )
743  {
744  f2 = f1;
745  fE2 = mfMSE;
746  f1 = ( f0 + f1 ) / 2;
747  }
748  else
749  {
750  f0 = f1;
751  fE0 = mfMSE;
752  f1 = ( f1 + f2 ) / 2;
753  }
754  mfGamma = f1;
755  refill();
756  }
757  if ( fE2 > fE0 )
758  {
759  if ( fE0 < mfMSE )
760  {
761  mfGamma = f0;
762  refill();
763  }
764  }
765  else
766  {
767  if ( fE2 < mfMSE )
768  {
769  mfGamma = f2;
770  refill();
771  }
772  }
773 }
774 
775 void ScETSForecastCalculation::refill()
776 {
777  // refill mpBase, mpTrend, mpPerIdx and mpForecast with values
778  // using the calculated mfAlpha, (mfBeta), mfGamma
779  // forecast 1 step ahead
780  for ( SCSIZE i = 1; i < mnCount; i++ )
781  {
782  if ( bEDS )
783  {
784  mpBase[ i ] = mfAlpha * maRange[ i ].Y +
785  ( 1 - mfAlpha ) * ( mpBase[ i - 1 ] + mpTrend[ i - 1 ] );
786  mpTrend[ i ] = mfGamma * ( mpBase[ i ] - mpBase[ i - 1 ] ) +
787  ( 1 - mfGamma ) * mpTrend[ i - 1 ];
788  mpForecast[ i ] = mpBase[ i - 1 ] + mpTrend[ i - 1 ];
789  }
790  else
791  {
792  SCSIZE nIdx;
793  if ( bAdditive )
794  {
795  nIdx = ( i > mnSmplInPrd ? i - mnSmplInPrd : i );
796  mpBase[ i ] = mfAlpha * ( maRange[ i ].Y - mpPerIdx[ nIdx ] ) +
797  ( 1 - mfAlpha ) * ( mpBase[ i - 1 ] + mpTrend[ i - 1 ] );
798  mpPerIdx[ i ] = mfBeta * ( maRange[ i ].Y - mpBase[ i ] ) +
799  ( 1 - mfBeta ) * mpPerIdx[ nIdx ];
800  }
801  else
802  {
803  nIdx = ( i >= mnSmplInPrd ? i - mnSmplInPrd : i );
804  mpBase[ i ] = mfAlpha * ( maRange[ i ].Y / mpPerIdx[ nIdx ] ) +
805  ( 1 - mfAlpha ) * ( mpBase[ i - 1 ] + mpTrend[ i - 1 ] );
806  mpPerIdx[ i ] = mfBeta * ( maRange[ i ].Y / mpBase[ i ] ) +
807  ( 1 - mfBeta ) * mpPerIdx[ nIdx ];
808  }
809  mpTrend[ i ] = mfGamma * ( mpBase[ i ] - mpBase[ i - 1 ] ) +
810  ( 1 - mfGamma ) * mpTrend[ i - 1 ];
811 
812  if ( bAdditive )
813  mpForecast[ i ] = mpBase[ i - 1 ] + mpTrend[ i - 1 ] + mpPerIdx[ nIdx ];
814  else
815  mpForecast[ i ] = ( mpBase[ i - 1 ] + mpTrend[ i - 1 ] ) * mpPerIdx[ nIdx ];
816  }
817  }
818  calcAccuracyIndicators();
819 }
820 
821 double ScETSForecastCalculation::convertXtoMonths( double x )
822 {
823  Date aDate = mpFormatter->GetNullDate() + static_cast< sal_Int32 >( x );
824  int nYear = aDate.GetYear();
825  int nMonth = aDate.GetMonth();
826  double fMonthLength;
827  switch ( nMonth )
828  {
829  case 1 :
830  case 3 :
831  case 5 :
832  case 7 :
833  case 8 :
834  case 10 :
835  case 12 :
836  fMonthLength = 31.0;
837  break;
838  case 2 :
839  fMonthLength = ( aDate.IsLeapYear() ? 29.0 : 28.0 );
840  break;
841  default :
842  fMonthLength = 30.0;
843  }
844  return ( 12.0 * nYear + nMonth + ( aDate.GetDay() - mnMonthDay ) / fMonthLength );
845 }
846 
847 void ScETSForecastCalculation::GetForecast( double fTarget, double& rForecast )
848 {
849  initCalc();
850 
851  if ( fTarget <= maRange[ mnCount - 1 ].X )
852  {
853  SCSIZE n = ( fTarget - maRange[ 0 ].X ) / mfStepSize;
854  double fInterpolate = fmod( fTarget - maRange[ 0 ].X, mfStepSize );
855  rForecast = maRange[ n ].Y;
856 
857  if ( fInterpolate >= cfMinABCResolution )
858  {
859  double fInterpolateFactor = fInterpolate / mfStepSize;
860  double fFc_1 = mpForecast[ n + 1 ];
861  rForecast = rForecast + fInterpolateFactor * ( fFc_1 - rForecast );
862  }
863  }
864  else
865  {
866  SCSIZE n = ( fTarget - maRange[ mnCount - 1 ].X ) / mfStepSize;
867  double fInterpolate = fmod( fTarget - maRange[ mnCount - 1 ].X, mfStepSize );
868 
869  if ( bEDS )
870  rForecast = mpBase[ mnCount - 1 ] + n * mpTrend[ mnCount - 1 ];
871  else if ( bAdditive )
872  rForecast = mpBase[ mnCount - 1 ] + n * mpTrend[ mnCount - 1 ] +
873  mpPerIdx[ mnCount - 1 - mnSmplInPrd + ( n % mnSmplInPrd ) ];
874  else
875  rForecast = ( mpBase[ mnCount - 1 ] + n * mpTrend[ mnCount - 1 ] ) *
876  mpPerIdx[ mnCount - 1 - mnSmplInPrd + ( n % mnSmplInPrd ) ];
877 
878  if ( fInterpolate >= cfMinABCResolution )
879  {
880  double fInterpolateFactor = fInterpolate / mfStepSize;
881  double fFc_1;
882  if ( bEDS )
883  fFc_1 = mpBase[ mnCount - 1 ] + ( n + 1 ) * mpTrend[ mnCount - 1 ];
884  else if ( bAdditive )
885  fFc_1 = mpBase[ mnCount - 1 ] + ( n + 1 ) * mpTrend[ mnCount - 1 ] +
886  mpPerIdx[ mnCount - 1 - mnSmplInPrd + ( ( n + 1 ) % mnSmplInPrd ) ];
887  else
888  fFc_1 = ( mpBase[ mnCount - 1 ] + ( n + 1 ) * mpTrend[ mnCount - 1 ] ) *
889  mpPerIdx[ mnCount - 1 - mnSmplInPrd + ( ( n + 1 ) % mnSmplInPrd ) ];
890  rForecast = rForecast + fInterpolateFactor * ( fFc_1 - rForecast );
891  }
892  }
893 }
894 
895 void ScETSForecastCalculation::GetForecastRange( const ScMatrixRef& rTMat, const ScMatrixRef& rFcMat )
896 {
897  SCSIZE nC, nR;
898  rTMat->GetDimensions( nC, nR );
899 
900  for ( SCSIZE i = 0; i < nR; i++ )
901  {
902  for ( SCSIZE j = 0; j < nC; j++ )
903  {
904  double fTarget;
905  if ( mnMonthDay )
906  fTarget = convertXtoMonths( rTMat->GetDouble( j, i ) );
907  else
908  fTarget = rTMat->GetDouble( j, i );
909  double fForecast;
910  GetForecast( fTarget, fForecast );
911  rFcMat->PutDouble( fForecast, j, i );
912  }
913  }
914 }
915 
916 void ScETSForecastCalculation::GetStatisticValue( const ScMatrixRef& rTypeMat, const ScMatrixRef& rStatMat )
917 {
918  initCalc();
919 
920  SCSIZE nC, nR;
921  rTypeMat->GetDimensions( nC, nR );
922  for ( SCSIZE i = 0; i < nR; i++ )
923  {
924  for ( SCSIZE j = 0; j < nC; j++ )
925  {
926  switch ( static_cast< int >( rTypeMat->GetDouble( j, i ) ) )
927  {
928  case 1 : // alpha
929  rStatMat->PutDouble( mfAlpha, j, i );
930  break;
931  case 2 : // gamma
932  rStatMat->PutDouble( mfGamma, j, i );
933  break;
934  case 3 : // beta
935  rStatMat->PutDouble( mfBeta, j, i );
936  break;
937  case 4 : // MASE
938  rStatMat->PutDouble( mfMASE, j, i );
939  break;
940  case 5 : // SMAPE
941  rStatMat->PutDouble( mfSMAPE, j, i );
942  break;
943  case 6 : // MAE
944  rStatMat->PutDouble( mfMAE, j, i );
945  break;
946  case 7 : // RMSE
947  rStatMat->PutDouble( mfRMSE, j, i );
948  break;
949  case 8 : // step size
950  rStatMat->PutDouble( mfStepSize, j, i );
951  break;
952  case 9 : // samples in period
953  rStatMat->PutDouble( mnSmplInPrd, j, i );
954  break;
955  }
956  }
957  }
958 }
959 
960 void ScETSForecastCalculation::GetSamplesInPeriod( double& rVal )
961 {
962  rVal = mnSmplInPrd;
963 }
964 
965 double ScETSForecastCalculation::RandDev()
966 {
967  // return a random deviation given the standard deviation
968  return ( mfRMSE * ScInterpreter::gaussinv(
970 }
971 
972 void ScETSForecastCalculation::GetETSPredictionIntervals( const ScMatrixRef& rTMat, const ScMatrixRef& rPIMat, double fPILevel )
973 {
974  initCalc();
975 
976  SCSIZE nC, nR;
977  rTMat->GetDimensions( nC, nR );
978 
979  // find maximum target value and calculate size of scenario-arrays
980  double fMaxTarget = rTMat->GetDouble( 0, 0 );
981  for ( SCSIZE i = 0; i < nR; i++ )
982  {
983  for ( SCSIZE j = 0; j < nC; j++ )
984  {
985  if ( fMaxTarget < rTMat->GetDouble( j, i ) )
986  fMaxTarget = rTMat->GetDouble( j, i );
987  }
988  }
989  if ( mnMonthDay )
990  fMaxTarget = convertXtoMonths( fMaxTarget ) - maRange[ mnCount - 1 ].X;
991  else
992  fMaxTarget -= maRange[ mnCount - 1 ].X;
993  SCSIZE nSize = fMaxTarget / mfStepSize;
994  if ( fmod( fMaxTarget, mfStepSize ) != 0.0 )
995  nSize++;
996 
997  if (nSize == 0)
998  {
999  mnErrorValue = FormulaError::IllegalArgument;
1000  return;
1001  }
1002 
1003  std::unique_ptr< double[] > xScenRange( new double[nSize]);
1004  std::unique_ptr< double[] > xScenBase( new double[nSize]);
1005  std::unique_ptr< double[] > xScenTrend( new double[nSize]);
1006  std::unique_ptr< double[] > xScenPerIdx( new double[nSize]);
1007  std::vector< std::vector< double > > aPredictions( nSize, std::vector< double >( cnScenarios ) );
1008 
1009  // fill scenarios
1010  for ( SCSIZE k = 0; k < cnScenarios; k++ )
1011  {
1012  // fill array with forecasts, with RandDev() added to xScenRange
1013  if ( bAdditive )
1014  {
1015  double nPIdx = !bEDS ? mpPerIdx[mnCount - mnSmplInPrd] : 0.0;
1016  // calculation based on additive model
1017  xScenRange[ 0 ] = mpBase[ mnCount - 1 ] + mpTrend[ mnCount - 1 ] +
1018  nPIdx +
1019  RandDev();
1020  aPredictions[ 0 ][ k ] = xScenRange[ 0 ];
1021  xScenBase[ 0 ] = mfAlpha * ( xScenRange[ 0 ] - nPIdx ) +
1022  ( 1 - mfAlpha ) * ( mpBase[ mnCount - 1 ] + mpTrend[ mnCount - 1 ] );
1023  xScenTrend[ 0 ] = mfGamma * ( xScenBase[ 0 ] - mpBase[ mnCount - 1 ] ) +
1024  ( 1 - mfGamma ) * mpTrend[ mnCount - 1 ];
1025  xScenPerIdx[ 0 ] = mfBeta * ( xScenRange[ 0 ] - xScenBase[ 0 ] ) +
1026  ( 1 - mfBeta ) * nPIdx;
1027  for ( SCSIZE i = 1; i < nSize; i++ )
1028  {
1029  double fPerIdx;
1030  if ( i < mnSmplInPrd )
1031  fPerIdx = mpPerIdx[ mnCount + i - mnSmplInPrd ];
1032  else
1033  fPerIdx = xScenPerIdx[ i - mnSmplInPrd ];
1034  xScenRange[ i ] = xScenBase[ i - 1 ] + xScenTrend[ i - 1 ] + fPerIdx + RandDev();
1035  aPredictions[ i ][ k ] = xScenRange[ i ];
1036  xScenBase[ i ] = mfAlpha * ( xScenRange[ i ] - fPerIdx ) +
1037  ( 1 - mfAlpha ) * ( xScenBase[ i - 1 ] + xScenTrend[ i - 1 ] );
1038  xScenTrend[ i ] = mfGamma * ( xScenBase[ i ] - xScenBase[ i - 1 ] ) +
1039  ( 1 - mfGamma ) * xScenTrend[ i - 1 ];
1040  xScenPerIdx[ i ] = mfBeta * ( xScenRange[ i ] - xScenBase[ i ] ) +
1041  ( 1 - mfBeta ) * fPerIdx;
1042  }
1043  }
1044  else
1045  {
1046  // calculation based on multiplicative model
1047  xScenRange[ 0 ] = ( mpBase[ mnCount - 1 ] + mpTrend[ mnCount - 1 ] ) *
1048  mpPerIdx[ mnCount - mnSmplInPrd ] +
1049  RandDev();
1050  aPredictions[ 0 ][ k ] = xScenRange[ 0 ];
1051  xScenBase[ 0 ] = mfAlpha * ( xScenRange[ 0 ] / mpPerIdx[ mnCount - mnSmplInPrd ] ) +
1052  ( 1 - mfAlpha ) * ( mpBase[ mnCount - 1 ] + mpTrend[ mnCount - 1 ] );
1053  xScenTrend[ 0 ] = mfGamma * ( xScenBase[ 0 ] - mpBase[ mnCount - 1 ] ) +
1054  ( 1 - mfGamma ) * mpTrend[ mnCount - 1 ];
1055  xScenPerIdx[ 0 ] = mfBeta * ( xScenRange[ 0 ] / xScenBase[ 0 ] ) +
1056  ( 1 - mfBeta ) * mpPerIdx[ mnCount - mnSmplInPrd ];
1057  for ( SCSIZE i = 1; i < nSize; i++ )
1058  {
1059  double fPerIdx;
1060  if ( i < mnSmplInPrd )
1061  fPerIdx = mpPerIdx[ mnCount + i - mnSmplInPrd ];
1062  else
1063  fPerIdx = xScenPerIdx[ i - mnSmplInPrd ];
1064  xScenRange[ i ] = ( xScenBase[ i - 1 ] + xScenTrend[ i - 1 ] ) * fPerIdx + RandDev();
1065  aPredictions[ i ][ k ] = xScenRange[ i ];
1066  xScenBase[ i ] = mfAlpha * ( xScenRange[ i ] / fPerIdx ) +
1067  ( 1 - mfAlpha ) * ( xScenBase[ i - 1 ] + xScenTrend[ i - 1 ] );
1068  xScenTrend[ i ] = mfGamma * ( xScenBase[ i ] - xScenBase[ i - 1 ] ) +
1069  ( 1 - mfGamma ) * xScenTrend[ i - 1 ];
1070  xScenPerIdx[ i ] = mfBeta * ( xScenRange[ i ] / xScenBase[ i ] ) +
1071  ( 1 - mfBeta ) * fPerIdx;
1072  }
1073  }
1074  }
1075 
1076  // create array of Percentile values;
1077  std::unique_ptr< double[] > xPercentile( new double[nSize]);
1078  for ( SCSIZE i = 0; i < nSize; i++ )
1079  {
1080  xPercentile[ i ] = ScInterpreter::GetPercentile( aPredictions[ i ], ( 1 + fPILevel ) / 2 ) -
1081  ScInterpreter::GetPercentile( aPredictions[ i ], 0.5 );
1082  }
1083 
1084  for ( SCSIZE i = 0; i < nR; i++ )
1085  {
1086  for ( SCSIZE j = 0; j < nC; j++ )
1087  {
1088  double fTarget;
1089  if ( mnMonthDay )
1090  fTarget = convertXtoMonths( rTMat->GetDouble( j, i ) ) - maRange[ mnCount - 1 ].X;
1091  else
1092  fTarget = rTMat->GetDouble( j, i ) - maRange[ mnCount - 1 ].X;
1093  SCSIZE nSteps = ( fTarget / mfStepSize ) - 1;
1094  double fFactor = fmod( fTarget, mfStepSize );
1095  double fPI = xPercentile[ nSteps ];
1096  if ( fFactor != 0.0 )
1097  {
1098  // interpolate
1099  double fPI1 = xPercentile[ nSteps + 1 ];
1100  fPI = fPI + fFactor * ( fPI1 - fPI );
1101  }
1102  rPIMat->PutDouble( fPI, j, i );
1103  }
1104  }
1105 }
1106 
1107 
1108 void ScETSForecastCalculation::GetEDSPredictionIntervals( const ScMatrixRef& rTMat, const ScMatrixRef& rPIMat, double fPILevel )
1109 {
1110  initCalc();
1111 
1112  SCSIZE nC, nR;
1113  rTMat->GetDimensions( nC, nR );
1114 
1115  // find maximum target value and calculate size of coefficient- array c
1116  double fMaxTarget = rTMat->GetDouble( 0, 0 );
1117  for ( SCSIZE i = 0; i < nR; i++ )
1118  {
1119  for ( SCSIZE j = 0; j < nC; j++ )
1120  {
1121  if ( fMaxTarget < rTMat->GetDouble( j, i ) )
1122  fMaxTarget = rTMat->GetDouble( j, i );
1123  }
1124  }
1125  if ( mnMonthDay )
1126  fMaxTarget = convertXtoMonths( fMaxTarget ) - maRange[ mnCount - 1 ].X;
1127  else
1128  fMaxTarget -= maRange[ mnCount - 1 ].X;
1129  SCSIZE nSize = fMaxTarget / mfStepSize;
1130  if ( fmod( fMaxTarget, mfStepSize ) != 0.0 )
1131  nSize++;
1132 
1133  if (nSize == 0)
1134  {
1135  mnErrorValue = FormulaError::IllegalArgument;
1136  return;
1137  }
1138 
1139  double z = ScInterpreter::gaussinv( ( 1.0 + fPILevel ) / 2.0 );
1140  double o = 1 - fPILevel;
1141  std::vector< double > c( nSize );
1142  for ( SCSIZE i = 0; i < nSize; i++ )
1143  {
1144  c[ i ] = sqrt( 1 + ( fPILevel / pow( 1 + o, 3.0 ) ) *
1145  ( ( 1 + 4 * o + 5 * o * o ) +
1146  2 * static_cast< double >( i ) * fPILevel * ( 1 + 3 * o ) +
1147  2 * static_cast< double >( i * i ) * fPILevel * fPILevel ) );
1148  }
1149 
1150 
1151  for ( SCSIZE i = 0; i < nR; i++ )
1152  {
1153  for ( SCSIZE j = 0; j < nC; j++ )
1154  {
1155  double fTarget;
1156  if ( mnMonthDay )
1157  fTarget = convertXtoMonths( rTMat->GetDouble( j, i ) ) - maRange[ mnCount - 1 ].X;
1158  else
1159  fTarget = rTMat->GetDouble( j, i ) - maRange[ mnCount - 1 ].X;
1160  SCSIZE nSteps = ( fTarget / mfStepSize ) - 1;
1161  double fFactor = fmod( fTarget, mfStepSize );
1162  double fPI = z * mfRMSE * c[ nSteps ] / c[ 0 ];
1163  if ( fFactor != 0.0 )
1164  {
1165  // interpolate
1166  double fPI1 = z * mfRMSE * c[ nSteps + 1 ] / c[ 0 ];
1167  fPI = fPI + fFactor * ( fPI1 - fPI );
1168  }
1169  rPIMat->PutDouble( fPI, j, i );
1170  }
1171  }
1172 }
1173 
1174 
1176 {
1178  switch ( eETSType )
1179  {
1180  case etsAdd :
1181  case etsMult :
1182  case etsStatAdd :
1183  case etsStatMult :
1184  if ( !MustHaveParamCount( nParamCount, 3, 6 ) )
1185  return;
1186  break;
1187  case etsPIAdd :
1188  case etsPIMult :
1189  if ( !MustHaveParamCount( nParamCount, 3, 7 ) )
1190  {
1191  return;
1192  }
1193  break;
1194  case etsSeason :
1195  if ( !MustHaveParamCount( nParamCount, 2, 4 ) )
1196  return;
1197  break;
1198  }
1199 
1200  int nAggregation;
1201  if ( ( nParamCount == 6 && eETSType != etsPIAdd && eETSType != etsPIMult ) ||
1202  ( nParamCount == 4 && eETSType == etsSeason ) ||
1203  nParamCount == 7 )
1204  nAggregation = static_cast< int >( GetDoubleWithDefault( 1.0 ) );
1205  else
1206  nAggregation = 1;
1207  if ( nAggregation < 1 || nAggregation > 7 )
1208  {
1210  return;
1211  }
1212 
1213  bool bDataCompletion;
1214  if ( ( nParamCount >= 5 && eETSType != etsPIAdd && eETSType != etsPIMult ) ||
1215  ( nParamCount >= 3 && eETSType == etsSeason ) ||
1216  ( nParamCount >= 6 && ( eETSType == etsPIAdd || eETSType == etsPIMult ) ) )
1217  {
1218  int nTemp = static_cast< int >( GetDoubleWithDefault( 1.0 ) );
1219  if ( nTemp == 0 || nTemp == 1 )
1220  bDataCompletion = nTemp;
1221  else
1222  {
1224  return;
1225  }
1226  }
1227  else
1228  bDataCompletion = true;
1229 
1230  int nSmplInPrd;
1231  if ( ( ( nParamCount >= 4 && eETSType != etsPIAdd && eETSType != etsPIMult ) ||
1232  ( nParamCount >= 5 && ( eETSType == etsPIAdd || eETSType == etsPIMult ) ) ) &&
1233  eETSType != etsSeason )
1234  {
1235  double fVal = GetDoubleWithDefault( 1.0 );
1236  if ( fmod( fVal, 1.0 ) != 0 || fVal < 0.0 )
1237  {
1238  PushError( FormulaError::IllegalFPOperation );
1239  return;
1240  }
1241  nSmplInPrd = static_cast< int >( fVal );
1242  }
1243  else
1244  nSmplInPrd = 1;
1245 
1246  // required arguments
1247  double fPILevel = 0.0;
1248  if ( nParamCount < 3 && ( nParamCount != 2 || eETSType != etsSeason ) )
1249  {
1251  return;
1252  }
1253 
1254  if ( eETSType == etsPIAdd || eETSType == etsPIMult )
1255  {
1256  fPILevel = (nParamCount < 4 ? 0.95 : GetDoubleWithDefault( 0.95 ));
1257  if ( fPILevel < 0 || fPILevel > 1 )
1258  {
1260  return;
1261  }
1262  }
1263 
1264  ScMatrixRef pTypeMat;
1265  if ( eETSType == etsStatAdd || eETSType == etsStatMult )
1266  {
1267  pTypeMat = GetMatrix();
1268  SCSIZE nC, nR;
1269  pTypeMat->GetDimensions( nC, nR );
1270  for ( SCSIZE i = 0; i < nR; i++ )
1271  {
1272  for ( SCSIZE j = 0; j < nC; j++ )
1273  {
1274  if ( static_cast< int >( pTypeMat->GetDouble( j, i ) ) < 1 ||
1275  static_cast< int >( pTypeMat->GetDouble( j, i ) ) > 9 )
1276  {
1278  return;
1279  }
1280  }
1281  }
1282  }
1283 
1284  ScMatrixRef pMatX = GetMatrix();
1285  ScMatrixRef pMatY = GetMatrix();
1286  if ( !pMatX || !pMatY )
1287  {
1289  return;
1290  }
1291  SCSIZE nCX, nCY;
1292  SCSIZE nRX, nRY;
1293  pMatX->GetDimensions( nCX, nRX );
1294  pMatY->GetDimensions( nCY, nRY );
1295  if ( nRX != nRY || nCX != nCY ||
1296  !pMatX->IsNumeric() || !pMatY->IsNumeric() )
1297  {
1299  return;
1300  }
1301 
1302  ScMatrixRef pTMat;
1303  if ( eETSType != etsStatAdd && eETSType != etsStatMult && eETSType != etsSeason )
1304  {
1305  pTMat = GetMatrix();
1306  if ( !pTMat )
1307  {
1309  return;
1310  }
1311  }
1312 
1313  ScETSForecastCalculation aETSCalc( pMatX->GetElementCount(), pFormatter );
1314  if ( !aETSCalc.PreprocessDataRange( pMatX, pMatY, nSmplInPrd, bDataCompletion,
1315  nAggregation,
1316  ( eETSType != etsStatAdd && eETSType != etsStatMult ? pTMat : nullptr ),
1317  eETSType ) )
1318  {
1319  PushError( aETSCalc.GetError() );
1320  return;
1321  }
1322 
1323  switch ( eETSType )
1324  {
1325  case etsAdd :
1326  case etsMult :
1327  {
1328  SCSIZE nC, nR;
1329  pTMat->GetDimensions( nC, nR );
1330  ScMatrixRef pFcMat = GetNewMat( nC, nR, /*bEmpty*/true );
1331  aETSCalc.GetForecastRange( pTMat, pFcMat );
1332  if (aETSCalc.GetError() != FormulaError::NONE)
1333  PushError( aETSCalc.GetError()); // explicitly push error, PushMatrix() does not
1334  else
1335  PushMatrix( pFcMat );
1336  }
1337  break;
1338  case etsPIAdd :
1339  case etsPIMult :
1340  {
1341  SCSIZE nC, nR;
1342  pTMat->GetDimensions( nC, nR );
1343  ScMatrixRef pPIMat = GetNewMat( nC, nR, /*bEmpty*/true );
1344  if ( nSmplInPrd == 0 )
1345  {
1346  aETSCalc.GetEDSPredictionIntervals( pTMat, pPIMat, fPILevel );
1347  }
1348  else
1349  {
1350  aETSCalc.GetETSPredictionIntervals( pTMat, pPIMat, fPILevel );
1351  }
1352  if (aETSCalc.GetError() != FormulaError::NONE)
1353  PushError( aETSCalc.GetError()); // explicitly push error, PushMatrix() does not
1354  else
1355  PushMatrix( pPIMat );
1356  }
1357  break;
1358  case etsStatAdd :
1359  case etsStatMult :
1360  {
1361  SCSIZE nC, nR;
1362  pTypeMat->GetDimensions( nC, nR );
1363  ScMatrixRef pStatMat = GetNewMat( nC, nR, /*bEmpty*/true );
1364  aETSCalc.GetStatisticValue( pTypeMat, pStatMat );
1365  if (aETSCalc.GetError() != FormulaError::NONE)
1366  PushError( aETSCalc.GetError()); // explicitly push error, PushMatrix() does not
1367  else
1368  PushMatrix( pStatMat );
1369  }
1370  break;
1371  case etsSeason :
1372  {
1373  double rVal;
1374  aETSCalc.GetSamplesInPeriod( rVal );
1375  SetError( aETSCalc.GetError() );
1376  PushDouble( rVal );
1377  }
1378  break;
1379  }
1380 }
1381 
1383 {
1384  OUStringBuffer aResBuf;
1385  short nParamCount = GetByte();
1386 
1387  //reverse order of parameter stack to simplify concatenation:
1388  ReverseStack( nParamCount );
1389 
1390  size_t nRefInList = 0;
1391  while ( nParamCount-- > 0 && nGlobalError == FormulaError::NONE )
1392  {
1393  switch ( GetStackType() )
1394  {
1395  case svString:
1396  case svDouble:
1397  {
1398  const OUString& rStr = GetString().getString();
1399  if (CheckStringResultLen( aResBuf, rStr))
1400  aResBuf.append( rStr);
1401  }
1402  break;
1403  case svSingleRef :
1404  {
1405  ScAddress aAdr;
1406  PopSingleRef( aAdr );
1407  if ( nGlobalError != FormulaError::NONE )
1408  break;
1409  ScRefCellValue aCell( mrDoc, aAdr );
1410  if (!aCell.hasEmptyValue())
1411  {
1412  svl::SharedString aSS;
1413  GetCellString( aSS, aCell);
1414  const OUString& rStr = aSS.getString();
1415  if (CheckStringResultLen( aResBuf, rStr))
1416  aResBuf.append( rStr);
1417  }
1418  }
1419  break;
1420  case svDoubleRef :
1421  case svRefList :
1422  {
1423  ScRange aRange;
1424  PopDoubleRef( aRange, nParamCount, nRefInList);
1425  if ( nGlobalError != FormulaError::NONE )
1426  break;
1427  // we need to read row for row, so we can't use ScCellIter
1428  SCCOL nCol1, nCol2;
1429  SCROW nRow1, nRow2;
1430  SCTAB nTab1, nTab2;
1431  aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
1432  if ( nTab1 != nTab2 )
1433  {
1434  SetError( FormulaError::IllegalParameter);
1435  break;
1436  }
1437  if ( nRow1 > nRow2 )
1438  std::swap( nRow1, nRow2 );
1439  if ( nCol1 > nCol2 )
1440  std::swap( nCol1, nCol2 );
1441  ScAddress aAdr;
1442  aAdr.SetTab( nTab1 );
1443  for ( SCROW nRow = nRow1; nRow <= nRow2; nRow++ )
1444  {
1445  for ( SCCOL nCol = nCol1; nCol <= nCol2; nCol++ )
1446  {
1447  aAdr.SetRow( nRow );
1448  aAdr.SetCol( nCol );
1449  ScRefCellValue aCell( mrDoc, aAdr );
1450  if (!aCell.hasEmptyValue() )
1451  {
1452  svl::SharedString aSS;
1453  GetCellString( aSS, aCell);
1454  const OUString& rStr = aSS.getString();
1455  if (CheckStringResultLen( aResBuf, rStr))
1456  aResBuf.append( rStr);
1457  }
1458  }
1459  }
1460  }
1461  break;
1462  case svMatrix :
1463  case svExternalSingleRef:
1464  case svExternalDoubleRef:
1465  {
1466  ScMatrixRef pMat = GetMatrix();
1467  if (pMat)
1468  {
1469  SCSIZE nC, nR;
1470  pMat->GetDimensions(nC, nR);
1471  if (nC == 0 || nR == 0)
1472  SetError(FormulaError::IllegalArgument);
1473  else
1474  {
1475  for (SCSIZE k = 0; k < nR; ++k)
1476  {
1477  for (SCSIZE j = 0; j < nC; ++j)
1478  {
1479  if ( pMat->IsStringOrEmpty( j, k ) )
1480  {
1481  const OUString& rStr = pMat->GetString( j, k ).getString();
1482  if (CheckStringResultLen( aResBuf, rStr))
1483  aResBuf.append( rStr);
1484  }
1485  else
1486  {
1487  if ( pMat->IsValue( j, k ) )
1488  {
1489  const OUString& rStr = pMat->GetString( *pFormatter, j, k ).getString();
1490  if (CheckStringResultLen( aResBuf, rStr))
1491  aResBuf.append( rStr);
1492  }
1493  }
1494  }
1495  }
1496  }
1497  }
1498  }
1499  break;
1500  default:
1501  PopError();
1502  SetError( FormulaError::IllegalArgument);
1503  break;
1504  }
1505  }
1506  PushString( aResBuf.makeStringAndClear() );
1507 }
1508 
1510 {
1511  short nParamCount = GetByte();
1512 
1513  if ( !MustHaveParamCountMin( nParamCount, 3 ) )
1514  return;
1515 
1516  //reverse order of parameter stack to simplify processing
1517  ReverseStack( nParamCount );
1518 
1519  // get aDelimiters and bSkipEmpty
1520  std::vector< OUString > aDelimiters;
1521  size_t nRefInList = 0;
1522  switch ( GetStackType() )
1523  {
1524  case svString:
1525  case svDouble:
1526  aDelimiters.push_back( GetString().getString() );
1527  break;
1528  case svSingleRef :
1529  {
1530  ScAddress aAdr;
1531  PopSingleRef( aAdr );
1532  if ( nGlobalError != FormulaError::NONE )
1533  break;
1534  ScRefCellValue aCell( mrDoc, aAdr );
1535  if (aCell.hasEmptyValue())
1536  aDelimiters.emplace_back("");
1537  else
1538  {
1539  svl::SharedString aSS;
1540  GetCellString( aSS, aCell);
1541  aDelimiters.push_back( aSS.getString());
1542  }
1543  }
1544  break;
1545  case svDoubleRef :
1546  case svRefList :
1547  {
1548  ScRange aRange;
1549  PopDoubleRef( aRange, nParamCount, nRefInList);
1550  if ( nGlobalError != FormulaError::NONE )
1551  break;
1552  // we need to read row for row, so we can't use ScCellIterator
1553  SCCOL nCol1, nCol2;
1554  SCROW nRow1, nRow2;
1555  SCTAB nTab1, nTab2;
1556  aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
1557  if ( nTab1 != nTab2 )
1558  {
1559  SetError( FormulaError::IllegalParameter);
1560  break;
1561  }
1562  if ( nRow1 > nRow2 )
1563  std::swap( nRow1, nRow2 );
1564  if ( nCol1 > nCol2 )
1565  std::swap( nCol1, nCol2 );
1566  ScAddress aAdr;
1567  aAdr.SetTab( nTab1 );
1568  for ( SCROW nRow = nRow1; nRow <= nRow2; nRow++ )
1569  {
1570  for ( SCCOL nCol = nCol1; nCol <= nCol2; nCol++ )
1571  {
1572  aAdr.SetRow( nRow );
1573  aAdr.SetCol( nCol );
1574  ScRefCellValue aCell( mrDoc, aAdr );
1575  if (aCell.hasEmptyValue())
1576  aDelimiters.emplace_back("");
1577  else
1578  {
1579  svl::SharedString aSS;
1580  GetCellString( aSS, aCell);
1581  aDelimiters.push_back( aSS.getString());
1582  }
1583  }
1584  }
1585  }
1586  break;
1587  case svMatrix :
1588  case svExternalSingleRef:
1589  case svExternalDoubleRef:
1590  {
1591  ScMatrixRef pMat = GetMatrix();
1592  if (pMat)
1593  {
1594  SCSIZE nC, nR;
1595  pMat->GetDimensions(nC, nR);
1596  if (nC == 0 || nR == 0)
1597  SetError(FormulaError::IllegalArgument);
1598  else
1599  {
1600  for (SCSIZE k = 0; k < nR; ++k)
1601  {
1602  for (SCSIZE j = 0; j < nC; ++j)
1603  {
1604  if (pMat->IsEmpty( j, k ))
1605  aDelimiters.emplace_back("");
1606  else if (pMat->IsStringOrEmpty( j, k ))
1607  aDelimiters.push_back( pMat->GetString( j, k ).getString() );
1608  else if (pMat->IsValue( j, k ))
1609  aDelimiters.push_back( pMat->GetString( *pFormatter, j, k ).getString() );
1610  else
1611  {
1612  assert(!"should this really happen?");
1613  aDelimiters.emplace_back("");
1614  }
1615  }
1616  }
1617  }
1618  }
1619  }
1620  break;
1621  default:
1622  PopError();
1623  SetError( FormulaError::IllegalArgument);
1624  break;
1625  }
1626  if ( aDelimiters.empty() )
1627  {
1629  return;
1630  }
1631  SCSIZE nSize = aDelimiters.size();
1632  bool bSkipEmpty = static_cast< bool >( GetDouble() );
1633  nParamCount -= 2;
1634 
1635  OUStringBuffer aResBuf;
1636  bool bFirst = true;
1637  SCSIZE nIdx = 0;
1638  nRefInList = 0;
1639  // get the strings to be joined
1640  while ( nParamCount-- > 0 && nGlobalError == FormulaError::NONE )
1641  {
1642  switch ( GetStackType() )
1643  {
1644  case svString:
1645  case svDouble:
1646  {
1647  OUString aStr = GetString().getString();
1648  if ( !aStr.isEmpty() || !bSkipEmpty )
1649  {
1650  if ( !bFirst )
1651  {
1652  aResBuf.append( aDelimiters[ nIdx ] );
1653  if ( nSize > 1 )
1654  {
1655  if ( ++nIdx >= nSize )
1656  nIdx = 0;
1657  }
1658  }
1659  else
1660  bFirst = false;
1661  if (CheckStringResultLen( aResBuf, aStr))
1662  aResBuf.append( aStr );
1663  }
1664  }
1665  break;
1666  case svSingleRef :
1667  {
1668  ScAddress aAdr;
1669  PopSingleRef( aAdr );
1670  if ( nGlobalError != FormulaError::NONE )
1671  break;
1672  ScRefCellValue aCell( mrDoc, aAdr );
1673  OUString aStr;
1674  if (!aCell.hasEmptyValue())
1675  {
1676  svl::SharedString aSS;
1677  GetCellString( aSS, aCell);
1678  aStr = aSS.getString();
1679  }
1680  if ( !aStr.isEmpty() || !bSkipEmpty )
1681  {
1682  if ( !bFirst )
1683  {
1684  aResBuf.append( aDelimiters[ nIdx ] );
1685  if ( nSize > 1 )
1686  {
1687  if ( ++nIdx >= nSize )
1688  nIdx = 0;
1689  }
1690  }
1691  else
1692  bFirst = false;
1693  if (CheckStringResultLen( aResBuf, aStr))
1694  aResBuf.append( aStr );
1695  }
1696  }
1697  break;
1698  case svDoubleRef :
1699  case svRefList :
1700  {
1701  ScRange aRange;
1702  PopDoubleRef( aRange, nParamCount, nRefInList);
1703  if ( nGlobalError != FormulaError::NONE )
1704  break;
1705  // we need to read row for row, so we can't use ScCellIterator
1706  SCCOL nCol1, nCol2;
1707  SCROW nRow1, nRow2;
1708  SCTAB nTab1, nTab2;
1709  aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
1710  if ( nTab1 != nTab2 )
1711  {
1712  SetError( FormulaError::IllegalParameter);
1713  break;
1714  }
1715  if ( nRow1 > nRow2 )
1716  std::swap( nRow1, nRow2 );
1717  if ( nCol1 > nCol2 )
1718  std::swap( nCol1, nCol2 );
1719  ScAddress aAdr;
1720  aAdr.SetTab( nTab1 );
1721  OUString aStr;
1722  for ( SCROW nRow = nRow1; nRow <= nRow2; nRow++ )
1723  {
1724  for ( SCCOL nCol = nCol1; nCol <= nCol2; nCol++ )
1725  {
1726  aAdr.SetRow( nRow );
1727  aAdr.SetCol( nCol );
1728  ScRefCellValue aCell( mrDoc, aAdr );
1729  if (aCell.hasEmptyValue())
1730  aStr.clear();
1731  else
1732  {
1733  svl::SharedString aSS;
1734  GetCellString( aSS, aCell);
1735  aStr = aSS.getString();
1736  }
1737  if ( !aStr.isEmpty() || !bSkipEmpty )
1738  {
1739  if ( !bFirst )
1740  {
1741  aResBuf.append( aDelimiters[ nIdx ] );
1742  if ( nSize > 1 )
1743  {
1744  if ( ++nIdx >= nSize )
1745  nIdx = 0;
1746  }
1747  }
1748  else
1749  bFirst = false;
1750  if (CheckStringResultLen( aResBuf, aStr))
1751  aResBuf.append( aStr );
1752  }
1753  }
1754  }
1755  }
1756  break;
1757  case svMatrix :
1758  case svExternalSingleRef:
1759  case svExternalDoubleRef:
1760  {
1761  ScMatrixRef pMat = GetMatrix();
1762  if (pMat)
1763  {
1764  SCSIZE nC, nR;
1765  pMat->GetDimensions(nC, nR);
1766  if (nC == 0 || nR == 0)
1767  SetError(FormulaError::IllegalArgument);
1768  else
1769  {
1770  OUString aStr;
1771  for (SCSIZE k = 0; k < nR; ++k)
1772  {
1773  for (SCSIZE j = 0; j < nC; ++j)
1774  {
1775  if (pMat->IsEmpty( j, k ) )
1776  aStr.clear();
1777  else if (pMat->IsStringOrEmpty( j, k ))
1778  aStr = pMat->GetString( j, k ).getString();
1779  else if (pMat->IsValue( j, k ))
1780  aStr = pMat->GetString( *pFormatter, j, k ).getString();
1781  else
1782  {
1783  assert(!"should this really happen?");
1784  aStr.clear();
1785  }
1786  if ( !aStr.isEmpty() || !bSkipEmpty )
1787  {
1788  if ( !bFirst )
1789  {
1790  aResBuf.append( aDelimiters[ nIdx ] );
1791  if ( nSize > 1 )
1792  {
1793  if ( ++nIdx >= nSize )
1794  nIdx = 0;
1795  }
1796  }
1797  else
1798  bFirst = false;
1799  if (CheckStringResultLen( aResBuf, aStr))
1800  aResBuf.append( aStr );
1801  }
1802  }
1803  }
1804  }
1805  }
1806  }
1807  break;
1808  case svMissing :
1809  {
1810  if ( !bSkipEmpty )
1811  {
1812  if ( !bFirst )
1813  {
1814  aResBuf.append( aDelimiters[ nIdx ] );
1815  if ( nSize > 1 )
1816  {
1817  if ( ++nIdx >= nSize )
1818  nIdx = 0;
1819  }
1820  }
1821  else
1822  bFirst = false;
1823  }
1824  }
1825  break;
1826  default:
1827  PopError();
1828  SetError( FormulaError::IllegalArgument);
1829  break;
1830  }
1831  }
1832  PushString( aResBuf.makeStringAndClear() );
1833 }
1834 
1835 
1837 {
1838  short nParamCount = GetByte();
1839 
1840  ReverseStack( nParamCount );
1841 
1842  nGlobalError = FormulaError::NONE; // propagate only for condition or active result path
1843  bool bFinished = false;
1844  while ( nParamCount > 0 && !bFinished && nGlobalError == FormulaError::NONE )
1845  {
1846  bool bVal = GetBool();
1847  nParamCount--;
1848  if ( bVal )
1849  {
1850  // TRUE
1851  if ( nParamCount < 1 )
1852  {
1853  // no parameter given for THEN
1855  return;
1856  }
1857  bFinished = true;
1858  }
1859  else
1860  {
1861  // FALSE
1862  if ( nParamCount >= 3 )
1863  {
1864  // ELSEIF path
1865  Pop();
1866  nParamCount--;
1867  }
1868  else
1869  {
1870  // no parameter given for ELSE
1871  PushNA();
1872  return;
1873  }
1874  }
1875  }
1876 
1877  if ( nGlobalError != FormulaError::NONE || !bFinished )
1878  {
1879  if ( !bFinished )
1880  PushNA(); // no true expression found
1881  if ( nGlobalError != FormulaError::NONE )
1882  PushNoValue(); // expression returned something other than true or false
1883  return;
1884  }
1885 
1886  //push result :
1887  FormulaConstTokenRef xToken( PopToken() );
1888  if ( xToken )
1889  {
1890  // Remove unused arguments of IFS from the stack before pushing the result.
1891  while ( nParamCount > 1 )
1892  {
1893  Pop();
1894  nParamCount--;
1895  }
1896  PushTokenRef( xToken );
1897  }
1898  else
1899  PushError( FormulaError::UnknownStackVariable );
1900 }
1901 
1902 
1904 {
1905  short nParamCount = GetByte();
1906 
1907  if (!MustHaveParamCountMin( nParamCount, 3))
1908  return;
1909 
1910  ReverseStack( nParamCount );
1911 
1912  nGlobalError = FormulaError::NONE; // propagate only for match or active result path
1913  bool isValue = false;
1914  double fRefVal = 0;
1915  svl::SharedString aRefStr;
1916  switch ( GetStackType() )
1917  {
1918  case svDouble:
1919  isValue = true;
1920  fRefVal = GetDouble();
1921  break;
1922  case svString:
1923  isValue = false;
1924  aRefStr = GetString();
1925  break;
1926  case svSingleRef :
1927  case svDoubleRef :
1928  {
1929  ScAddress aAdr;
1930  if (!PopDoubleRefOrSingleRef( aAdr ))
1931  break;
1932  ScRefCellValue aCell( mrDoc, aAdr );
1933  isValue = !( aCell.hasString() || aCell.hasEmptyValue() || aCell.isEmpty() );
1934  if ( isValue )
1935  fRefVal = GetCellValue( aAdr, aCell);
1936  else
1937  GetCellString( aRefStr, aCell);
1938  }
1939  break;
1940  case svExternalSingleRef:
1941  case svExternalDoubleRef:
1942  case svMatrix:
1943  isValue = ScMatrix::IsValueType( GetDoubleOrStringFromMatrix( fRefVal, aRefStr ) );
1944  break;
1945  default :
1946  PopError();
1948  return;
1949  }
1950  nParamCount--;
1951  bool bFinished = false;
1952  while ( nParamCount > 1 && !bFinished && nGlobalError == FormulaError::NONE )
1953  {
1954  double fVal = 0;
1956  if ( isValue )
1957  fVal = GetDouble();
1958  else
1959  aStr = GetString();
1960  nParamCount--;
1961  if ((nGlobalError != FormulaError::NONE && nParamCount < 2)
1962  || (isValue && rtl::math::approxEqual( fRefVal, fVal))
1963  || (!isValue && aRefStr.getDataIgnoreCase() == aStr.getDataIgnoreCase()))
1964  {
1965  // TRUE
1966  bFinished = true;
1967  }
1968  else
1969  {
1970  // FALSE
1971  if ( nParamCount >= 2 )
1972  {
1973  // ELSEIF path
1974  Pop();
1975  nParamCount--;
1976  // if nParamCount equals 1: default value to be returned
1977  bFinished = ( nParamCount == 1 );
1978  }
1979  else
1980  {
1981  // no parameter given for ELSE
1982  PushNA();
1983  return;
1984  }
1985  nGlobalError = FormulaError::NONE;
1986  }
1987  }
1988 
1989  if ( nGlobalError != FormulaError::NONE || !bFinished )
1990  {
1991  if ( !bFinished )
1992  PushNA(); // no true expression found
1993  else
1995  return;
1996  }
1997 
1998  // push result
1999  FormulaConstTokenRef xToken( PopToken() );
2000  if ( xToken )
2001  {
2002  // Remove unused arguments of SWITCH from the stack before pushing the result.
2003  while ( nParamCount > 1 )
2004  {
2005  Pop();
2006  nParamCount--;
2007  }
2008  PushTokenRef( xToken );
2009  }
2010  else
2011  PushError( FormulaError::UnknownStackVariable );
2012 }
2013 
2014 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
svString
::boost::intrusive_ptr< const FormulaToken > FormulaConstTokenRef
svRefList
OUString getString() const
void ScSwitch_MS()
Definition: interpr8.cxx:1903
static double GetPercentile(::std::vector< double > &rArray, double fPercentile)
Definition: interpr3.cxx:3401
void PushIllegalParameter()
Definition: interpr4.cxx:1902
rtl_uString * getDataIgnoreCase()
bool isEmpty() const
Definition: cellvalue.cxx:670
void ScTextJoin_MS()
Definition: interpr8.cxx:1509
void ReverseStack(sal_uInt8 nParamCount)
Definition: interpr4.cxx:1973
ScMatrixRef GetMatrix()
Definition: interpr5.cxx:477
sal_Int64 n
void PopSingleRef(ScAddress &)
Definition: interpr4.cxx:902
bool IsLeapYear() const
void ScConcat_MS()
Definition: interpr8.cxx:1382
void ScIfs_MS()
Definition: interpr8.cxx:1836
This is very similar to ScCellValue, except that it references the original value instead of copying ...
Definition: cellvalue.hxx:103
double GetCellValue(const ScAddress &, ScRefCellValue &rCell)
Definition: interpr4.cxx:176
constexpr double get() const
Returns the final sum.
Definition: kahan.hxx:207
double GetDoubleWithDefault(double nDefault)
Definition: interpr4.cxx:2142
bool PopDoubleRefOrSingleRef(ScAddress &rAdr)
Definition: interpr4.cxx:1317
NONE
SvNumberFormatter * pFormatter
Definition: interpre.hxx:203
void PushParameterExpected()
Definition: interpr4.cxx:1897
char sal_uInt16 & nParamCount
Definition: callform.cxx:53
This class provides LO with Kahan summation algorithm About this algorithm: https://en.wikipedia.org/wiki/Kahan_summation_algorithm For general purpose software we assume first order error is enough.
Definition: kahan.hxx:23
size_t SCSIZE
size_t typedef to be able to find places where code was changed from USHORT to size_t and is used to ...
Definition: address.hxx:44
ScMatrixRef GetNewMat(SCSIZE nC, SCSIZE nR, bool bEmpty=false)
Definition: interpr5.cxx:297
sal_uInt16 GetMonth() const
bool hasString() const
Definition: cellvalue.cxx:617
static double gaussinv(double x)
Definition: interpr3.cxx:269
ScETSType
Definition: interpre.hxx:118
svDouble
::boost::intrusive_ptr< ScMatrix > ScMatrixRef
Definition: types.hxx:25
void PushString(const OUString &rStr)
Definition: interpr4.cxx:1789
void SetRow(SCROW nRowP)
Definition: address.hxx:274
bool hasEmptyValue()
Definition: cellvalue.cxx:675
void PushError(FormulaError nError)
Definition: interpr4.cxx:1891
void SetCol(SCCOL nColP)
Definition: address.hxx:278
double GetDouble()
Definition: interpr4.cxx:2061
sal_Int16 GetYear() const
bool MustHaveParamCount(short nAct, short nMust)
Definition: interpre.hxx:1055
void SetTab(SCTAB nTabP)
Definition: address.hxx:282
formula::FormulaConstTokenRef PopToken()
Definition: interpr4.cxx:761
ScMatValType GetDoubleOrStringFromMatrix(double &rDouble, svl::SharedString &rString)
Definition: interpr4.cxx:2399
int i
void PushDouble(double nVal)
Definition: interpr4.cxx:1765
sal_Int16 SCCOL
Definition: types.hxx:21
static bool IsValueType(ScMatValType nType)
Value or boolean.
Definition: scmatrix.hxx:178
void PopDoubleRef(ScRange &rRange, short &rParam, size_t &rRefInList)
If formula::StackVar formula::svDoubleRef pop ScDoubleRefToken and return values of ScComplexRefData...
Definition: interpr4.cxx:1042
bool MustHaveParamCountMin(short nAct, short nMin)
Definition: interpre.hxx:1077
void PushMatrix(const sc::RangeMatrix &rMat)
Definition: interpr4.cxx:1866
svExternalDoubleRef
sal_uInt16 GetDay() const
void GetCellString(svl::SharedString &rStr, ScRefCellValue &rCell)
Definition: interpr4.cxx:243
svExternalSingleRef
FormulaError nGlobalError
Definition: interpre.hxx:208
void GetVars(SCCOL &nCol1, SCROW &nRow1, SCTAB &nTab1, SCCOL &nCol2, SCROW &nRow2, SCTAB &nTab2) const
Definition: address.hxx:692
std::size_t mnCount
formula::StackVar GetStackType()
Stack type with replacement of defaults, e.g. svMissing and formula::svEmptyCell will result in formu...
Definition: interpr4.cxx:1942
double uniform_real_distribution(double a=0.0, double b=1.0)
svSingleRef
#define Y
FormulaError
sal_uInt8 GetByte() const
Definition: interpre.hxx:423
svDoubleRef
void PushIllegalArgument()
Definition: interpr4.cxx:1907
sal_Int32 SCROW
Definition: types.hxx:17
unsigned char sal_uInt8
void ScForecast_Ets(ScETSType eETSType)
Definition: interpr8.cxx:1175
float z
ScDocument & mrDoc
Definition: interpre.hxx:196
svMatrix
int GetError()
svl::SharedString GetString()
Definition: interpr4.cxx:2298
void PushNoValue()
Definition: interpr4.cxx:1917
void PopError()
Definition: interpr4.cxx:749
#define SAL_WARN(area, stream)
bool CheckStringResultLen(OUString &rResult, const OUString &rAdd)
Definition: interpre.hxx:1116
void PushTokenRef(const formula::FormulaConstTokenRef &)
Pushes the token or substitutes with formula::FormulaErrorToken in case nGlobalError is set and the t...
Definition: interpr4.cxx:662
OUString getString(const Any &_rAny)
svMissing
void SetError(FormulaError nError)
Definition: interpre.hxx:1015
aStr
bool GetBool()
Definition: interpre.hxx:441
static bool lcl_SortByX(const DataPoint &lhs, const DataPoint &rhs)
Definition: interpr8.cxx:36
B2DRange maRange
sal_Int16 SCTAB
Definition: types.hxx:22