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.getLength()))
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.getLength()))
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  PutInOrder( nRow1, nRow2 );
1438  PutInOrder( nCol1, nCol2 );
1439  ScAddress aAdr;
1440  aAdr.SetTab( nTab1 );
1441  for ( SCROW nRow = nRow1; nRow <= nRow2; nRow++ )
1442  {
1443  for ( SCCOL nCol = nCol1; nCol <= nCol2; nCol++ )
1444  {
1445  aAdr.SetRow( nRow );
1446  aAdr.SetCol( nCol );
1447  ScRefCellValue aCell( mrDoc, aAdr );
1448  if (!aCell.hasEmptyValue() )
1449  {
1450  svl::SharedString aSS;
1451  GetCellString( aSS, aCell);
1452  const OUString& rStr = aSS.getString();
1453  if (CheckStringResultLen(aResBuf, rStr.getLength()))
1454  aResBuf.append( rStr);
1455  }
1456  }
1457  }
1458  }
1459  break;
1460  case svMatrix :
1461  case svExternalSingleRef:
1462  case svExternalDoubleRef:
1463  {
1464  ScMatrixRef pMat = GetMatrix();
1465  if (pMat)
1466  {
1467  SCSIZE nC, nR;
1468  pMat->GetDimensions(nC, nR);
1469  if (nC == 0 || nR == 0)
1470  SetError(FormulaError::IllegalArgument);
1471  else
1472  {
1473  for (SCSIZE k = 0; k < nR; ++k)
1474  {
1475  for (SCSIZE j = 0; j < nC; ++j)
1476  {
1477  if ( pMat->IsStringOrEmpty( j, k ) )
1478  {
1479  const OUString& rStr = pMat->GetString( j, k ).getString();
1480  if (CheckStringResultLen(aResBuf, rStr.getLength()))
1481  aResBuf.append( rStr);
1482  }
1483  else
1484  {
1485  if ( pMat->IsValue( j, k ) )
1486  {
1487  const OUString& rStr = pMat->GetString( *pFormatter, j, k ).getString();
1488  if (CheckStringResultLen(aResBuf, rStr.getLength()))
1489  aResBuf.append( rStr);
1490  }
1491  }
1492  }
1493  }
1494  }
1495  }
1496  }
1497  break;
1498  default:
1499  PopError();
1500  SetError( FormulaError::IllegalArgument);
1501  break;
1502  }
1503  }
1504  PushString( aResBuf.makeStringAndClear() );
1505 }
1506 
1508 {
1509  short nParamCount = GetByte();
1510 
1511  if ( !MustHaveParamCountMin( nParamCount, 3 ) )
1512  return;
1513 
1514  //reverse order of parameter stack to simplify processing
1515  ReverseStack( nParamCount );
1516 
1517  // get aDelimiters and bSkipEmpty
1518  std::vector< OUString > aDelimiters;
1519  size_t nRefInList = 0;
1520  switch ( GetStackType() )
1521  {
1522  case svString:
1523  case svDouble:
1524  aDelimiters.push_back( GetString().getString() );
1525  break;
1526  case svSingleRef :
1527  {
1528  ScAddress aAdr;
1529  PopSingleRef( aAdr );
1530  if ( nGlobalError != FormulaError::NONE )
1531  break;
1532  ScRefCellValue aCell( mrDoc, aAdr );
1533  if (aCell.hasEmptyValue())
1534  aDelimiters.emplace_back("");
1535  else
1536  {
1537  svl::SharedString aSS;
1538  GetCellString( aSS, aCell);
1539  aDelimiters.push_back( aSS.getString());
1540  }
1541  }
1542  break;
1543  case svDoubleRef :
1544  case svRefList :
1545  {
1546  ScRange aRange;
1547  PopDoubleRef( aRange, nParamCount, nRefInList);
1548  if ( nGlobalError != FormulaError::NONE )
1549  break;
1550  // we need to read row for row, so we can't use ScCellIterator
1551  SCCOL nCol1, nCol2;
1552  SCROW nRow1, nRow2;
1553  SCTAB nTab1, nTab2;
1554  aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
1555  if ( nTab1 != nTab2 )
1556  {
1557  SetError( FormulaError::IllegalParameter);
1558  break;
1559  }
1560  PutInOrder( nRow1, nRow2 );
1561  PutInOrder( nCol1, nCol2 );
1562  ScAddress aAdr;
1563  aAdr.SetTab( nTab1 );
1564  for ( SCROW nRow = nRow1; nRow <= nRow2; nRow++ )
1565  {
1566  for ( SCCOL nCol = nCol1; nCol <= nCol2; nCol++ )
1567  {
1568  aAdr.SetRow( nRow );
1569  aAdr.SetCol( nCol );
1570  ScRefCellValue aCell( mrDoc, aAdr );
1571  if (aCell.hasEmptyValue())
1572  aDelimiters.emplace_back("");
1573  else
1574  {
1575  svl::SharedString aSS;
1576  GetCellString( aSS, aCell);
1577  aDelimiters.push_back( aSS.getString());
1578  }
1579  }
1580  }
1581  }
1582  break;
1583  case svMatrix :
1584  case svExternalSingleRef:
1585  case svExternalDoubleRef:
1586  {
1587  ScMatrixRef pMat = GetMatrix();
1588  if (pMat)
1589  {
1590  SCSIZE nC, nR;
1591  pMat->GetDimensions(nC, nR);
1592  if (nC == 0 || nR == 0)
1593  SetError(FormulaError::IllegalArgument);
1594  else
1595  {
1596  for (SCSIZE k = 0; k < nR; ++k)
1597  {
1598  for (SCSIZE j = 0; j < nC; ++j)
1599  {
1600  if (pMat->IsEmpty( j, k ))
1601  aDelimiters.emplace_back("");
1602  else if (pMat->IsStringOrEmpty( j, k ))
1603  aDelimiters.push_back( pMat->GetString( j, k ).getString() );
1604  else if (pMat->IsValue( j, k ))
1605  aDelimiters.push_back( pMat->GetString( *pFormatter, j, k ).getString() );
1606  else
1607  {
1608  assert(!"should this really happen?");
1609  aDelimiters.emplace_back("");
1610  }
1611  }
1612  }
1613  }
1614  }
1615  }
1616  break;
1617  default:
1618  PopError();
1619  SetError( FormulaError::IllegalArgument);
1620  break;
1621  }
1622  if ( aDelimiters.empty() )
1623  {
1625  return;
1626  }
1627  SCSIZE nSize = aDelimiters.size();
1628  bool bSkipEmpty = static_cast< bool >( GetDouble() );
1629  nParamCount -= 2;
1630 
1631  OUStringBuffer aResBuf;
1632  bool bFirst = true;
1633  SCSIZE nIdx = 0;
1634  nRefInList = 0;
1635  // get the strings to be joined
1636  while ( nParamCount-- > 0 && nGlobalError == FormulaError::NONE )
1637  {
1638  switch ( GetStackType() )
1639  {
1640  case svString:
1641  case svDouble:
1642  {
1643  OUString aStr = GetString().getString();
1644  if ( !aStr.isEmpty() || !bSkipEmpty )
1645  {
1646  if ( !bFirst )
1647  {
1648  aResBuf.append( aDelimiters[ nIdx ] );
1649  if ( nSize > 1 )
1650  {
1651  if ( ++nIdx >= nSize )
1652  nIdx = 0;
1653  }
1654  }
1655  else
1656  bFirst = false;
1657  if (CheckStringResultLen(aResBuf, aStr.getLength()))
1658  aResBuf.append( aStr );
1659  }
1660  }
1661  break;
1662  case svSingleRef :
1663  {
1664  ScAddress aAdr;
1665  PopSingleRef( aAdr );
1666  if ( nGlobalError != FormulaError::NONE )
1667  break;
1668  ScRefCellValue aCell( mrDoc, aAdr );
1669  OUString aStr;
1670  if (!aCell.hasEmptyValue())
1671  {
1672  svl::SharedString aSS;
1673  GetCellString( aSS, aCell);
1674  aStr = aSS.getString();
1675  }
1676  if ( !aStr.isEmpty() || !bSkipEmpty )
1677  {
1678  if ( !bFirst )
1679  {
1680  aResBuf.append( aDelimiters[ nIdx ] );
1681  if ( nSize > 1 )
1682  {
1683  if ( ++nIdx >= nSize )
1684  nIdx = 0;
1685  }
1686  }
1687  else
1688  bFirst = false;
1689  if (CheckStringResultLen(aResBuf, aStr.getLength()))
1690  aResBuf.append( aStr );
1691  }
1692  }
1693  break;
1694  case svDoubleRef :
1695  case svRefList :
1696  {
1697  ScRange aRange;
1698  PopDoubleRef( aRange, nParamCount, nRefInList);
1699  if ( nGlobalError != FormulaError::NONE )
1700  break;
1701  // we need to read row for row, so we can't use ScCellIterator
1702  SCCOL nCol1, nCol2;
1703  SCROW nRow1, nRow2;
1704  SCTAB nTab1, nTab2;
1705  aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
1706  if ( nTab1 != nTab2 )
1707  {
1708  SetError( FormulaError::IllegalParameter);
1709  break;
1710  }
1711  PutInOrder( nRow1, nRow2 );
1712  PutInOrder( nCol1, nCol2 );
1713  ScAddress aAdr;
1714  aAdr.SetTab( nTab1 );
1715  OUString aStr;
1716  for ( SCROW nRow = nRow1; nRow <= nRow2; nRow++ )
1717  {
1718  for ( SCCOL nCol = nCol1; nCol <= nCol2; nCol++ )
1719  {
1720  aAdr.SetRow( nRow );
1721  aAdr.SetCol( nCol );
1722  ScRefCellValue aCell( mrDoc, aAdr );
1723  if (aCell.hasEmptyValue())
1724  aStr.clear();
1725  else
1726  {
1727  svl::SharedString aSS;
1728  GetCellString( aSS, aCell);
1729  aStr = aSS.getString();
1730  }
1731  if ( !aStr.isEmpty() || !bSkipEmpty )
1732  {
1733  if ( !bFirst )
1734  {
1735  aResBuf.append( aDelimiters[ nIdx ] );
1736  if ( nSize > 1 )
1737  {
1738  if ( ++nIdx >= nSize )
1739  nIdx = 0;
1740  }
1741  }
1742  else
1743  bFirst = false;
1744  if (CheckStringResultLen(aResBuf, aStr.getLength()))
1745  aResBuf.append( aStr );
1746  }
1747  }
1748  }
1749  }
1750  break;
1751  case svMatrix :
1752  case svExternalSingleRef:
1753  case svExternalDoubleRef:
1754  {
1755  ScMatrixRef pMat = GetMatrix();
1756  if (pMat)
1757  {
1758  SCSIZE nC, nR;
1759  pMat->GetDimensions(nC, nR);
1760  if (nC == 0 || nR == 0)
1761  SetError(FormulaError::IllegalArgument);
1762  else
1763  {
1764  OUString aStr;
1765  for (SCSIZE k = 0; k < nR; ++k)
1766  {
1767  for (SCSIZE j = 0; j < nC; ++j)
1768  {
1769  if (pMat->IsEmpty( j, k ) )
1770  aStr.clear();
1771  else if (pMat->IsStringOrEmpty( j, k ))
1772  aStr = pMat->GetString( j, k ).getString();
1773  else if (pMat->IsValue( j, k ))
1774  aStr = pMat->GetString( *pFormatter, j, k ).getString();
1775  else
1776  {
1777  assert(!"should this really happen?");
1778  aStr.clear();
1779  }
1780  if ( !aStr.isEmpty() || !bSkipEmpty )
1781  {
1782  if ( !bFirst )
1783  {
1784  aResBuf.append( aDelimiters[ nIdx ] );
1785  if ( nSize > 1 )
1786  {
1787  if ( ++nIdx >= nSize )
1788  nIdx = 0;
1789  }
1790  }
1791  else
1792  bFirst = false;
1793  if (CheckStringResultLen(aResBuf, aStr.getLength()))
1794  aResBuf.append( aStr );
1795  }
1796  }
1797  }
1798  }
1799  }
1800  }
1801  break;
1802  case svMissing :
1803  {
1804  if ( !bSkipEmpty )
1805  {
1806  if ( !bFirst )
1807  {
1808  aResBuf.append( aDelimiters[ nIdx ] );
1809  if ( nSize > 1 )
1810  {
1811  if ( ++nIdx >= nSize )
1812  nIdx = 0;
1813  }
1814  }
1815  else
1816  bFirst = false;
1817  }
1818  }
1819  break;
1820  default:
1821  PopError();
1822  SetError( FormulaError::IllegalArgument);
1823  break;
1824  }
1825  }
1826  PushString( aResBuf.makeStringAndClear() );
1827 }
1828 
1829 
1831 {
1832  short nParamCount = GetByte();
1833 
1834  ReverseStack( nParamCount );
1835 
1836  nGlobalError = FormulaError::NONE; // propagate only for condition or active result path
1837  bool bFinished = false;
1838  while ( nParamCount > 0 && !bFinished && nGlobalError == FormulaError::NONE )
1839  {
1840  bool bVal = GetBool();
1841  nParamCount--;
1842  if ( bVal )
1843  {
1844  // TRUE
1845  if ( nParamCount < 1 )
1846  {
1847  // no parameter given for THEN
1849  return;
1850  }
1851  bFinished = true;
1852  }
1853  else
1854  {
1855  // FALSE
1856  if ( nParamCount >= 3 )
1857  {
1858  // ELSEIF path
1859  Pop();
1860  nParamCount--;
1861  }
1862  else
1863  {
1864  // no parameter given for ELSE
1865  PushNA();
1866  return;
1867  }
1868  }
1869  }
1870 
1871  if ( nGlobalError != FormulaError::NONE || !bFinished )
1872  {
1873  if ( !bFinished )
1874  PushNA(); // no true expression found
1875  if ( nGlobalError != FormulaError::NONE )
1876  PushNoValue(); // expression returned something other than true or false
1877  return;
1878  }
1879 
1880  //push result :
1881  FormulaConstTokenRef xToken( PopToken() );
1882  if ( xToken )
1883  {
1884  // Remove unused arguments of IFS from the stack before pushing the result.
1885  while ( nParamCount > 1 )
1886  {
1887  Pop();
1888  nParamCount--;
1889  }
1890  PushTokenRef( xToken );
1891  }
1892  else
1893  PushError( FormulaError::UnknownStackVariable );
1894 }
1895 
1896 
1898 {
1899  short nParamCount = GetByte();
1900 
1901  if (!MustHaveParamCountMin( nParamCount, 3))
1902  return;
1903 
1904  ReverseStack( nParamCount );
1905 
1906  nGlobalError = FormulaError::NONE; // propagate only for match or active result path
1907  bool isValue = false;
1908  double fRefVal = 0;
1909  svl::SharedString aRefStr;
1910  switch ( GetStackType() )
1911  {
1912  case svDouble:
1913  isValue = true;
1914  fRefVal = GetDouble();
1915  break;
1916  case svString:
1917  isValue = false;
1918  aRefStr = GetString();
1919  break;
1920  case svSingleRef :
1921  case svDoubleRef :
1922  {
1923  ScAddress aAdr;
1924  if (!PopDoubleRefOrSingleRef( aAdr ))
1925  break;
1926  ScRefCellValue aCell( mrDoc, aAdr );
1927  isValue = !( aCell.hasString() || aCell.hasEmptyValue() || aCell.isEmpty() );
1928  if ( isValue )
1929  fRefVal = GetCellValue( aAdr, aCell);
1930  else
1931  GetCellString( aRefStr, aCell);
1932  }
1933  break;
1934  case svExternalSingleRef:
1935  case svExternalDoubleRef:
1936  case svMatrix:
1937  isValue = ScMatrix::IsValueType( GetDoubleOrStringFromMatrix( fRefVal, aRefStr ) );
1938  break;
1939  default :
1940  PopError();
1942  return;
1943  }
1944  nParamCount--;
1945  bool bFinished = false;
1946  while ( nParamCount > 1 && !bFinished && nGlobalError == FormulaError::NONE )
1947  {
1948  double fVal = 0;
1950  if ( isValue )
1951  fVal = GetDouble();
1952  else
1953  aStr = GetString();
1954  nParamCount--;
1955  if ((nGlobalError != FormulaError::NONE && nParamCount < 2)
1956  || (isValue && rtl::math::approxEqual( fRefVal, fVal))
1957  || (!isValue && aRefStr.getDataIgnoreCase() == aStr.getDataIgnoreCase()))
1958  {
1959  // TRUE
1960  bFinished = true;
1961  }
1962  else
1963  {
1964  // FALSE
1965  if ( nParamCount >= 2 )
1966  {
1967  // ELSEIF path
1968  Pop();
1969  nParamCount--;
1970  // if nParamCount equals 1: default value to be returned
1971  bFinished = ( nParamCount == 1 );
1972  }
1973  else
1974  {
1975  // no parameter given for ELSE
1976  PushNA();
1977  return;
1978  }
1979  nGlobalError = FormulaError::NONE;
1980  }
1981  }
1982 
1983  if ( nGlobalError != FormulaError::NONE || !bFinished )
1984  {
1985  if ( !bFinished )
1986  PushNA(); // no true expression found
1987  else
1989  return;
1990  }
1991 
1992  // push result
1993  FormulaConstTokenRef xToken( PopToken() );
1994  if ( xToken )
1995  {
1996  // Remove unused arguments of SWITCH from the stack before pushing the result.
1997  while ( nParamCount > 1 )
1998  {
1999  Pop();
2000  nParamCount--;
2001  }
2002  PushTokenRef( xToken );
2003  }
2004  else
2005  PushError( FormulaError::UnknownStackVariable );
2006 }
2007 
2008 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
svString
::boost::intrusive_ptr< const FormulaToken > FormulaConstTokenRef
svRefList
OUString getString() const
void ScSwitch_MS()
Definition: interpr8.cxx:1897
static double GetPercentile(::std::vector< double > &rArray, double fPercentile)
Definition: interpr3.cxx:3400
void PushIllegalParameter()
Definition: interpr4.cxx:1926
rtl_uString * getDataIgnoreCase()
bool isEmpty() const
Definition: cellvalue.cxx:670
void ScTextJoin_MS()
Definition: interpr8.cxx:1507
void ReverseStack(sal_uInt8 nParamCount)
Definition: interpr4.cxx:1997
ScMatrixRef GetMatrix()
Definition: interpr5.cxx:478
sal_Int64 n
void PopSingleRef(ScAddress &)
Definition: interpr4.cxx:906
bool IsLeapYear() const
void ScConcat_MS()
Definition: interpr8.cxx:1382
void ScIfs_MS()
Definition: interpr8.cxx:1830
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:177
double GetDoubleWithDefault(double nDefault)
Definition: interpr4.cxx:2166
bool PopDoubleRefOrSingleRef(ScAddress &rAdr)
Definition: interpr4.cxx:1341
NONE
SvNumberFormatter * pFormatter
Definition: interpre.hxx:193
void PushParameterExpected()
Definition: interpr4.cxx:1921
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:25
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:298
sal_uInt16 GetMonth() const
bool hasString() const
Definition: cellvalue.cxx:617
static double gaussinv(double x)
Definition: interpr3.cxx:268
ScETSType
Definition: interpre.hxx:119
svDouble
::boost::intrusive_ptr< ScMatrix > ScMatrixRef
Definition: types.hxx:25
void PushString(const OUString &rStr)
Definition: interpr4.cxx:1813
void SetRow(SCROW nRowP)
Definition: address.hxx:287
bool hasEmptyValue()
Definition: cellvalue.cxx:675
void PushError(FormulaError nError)
Definition: interpr4.cxx:1915
void SetCol(SCCOL nColP)
Definition: address.hxx:291
double GetDouble()
Definition: interpr4.cxx:2085
void PutInOrder(T &nStart, T &nEnd)
Definition: address.hxx:150
sal_Int16 GetYear() const
bool MustHaveParamCount(short nAct, short nMust)
Definition: interpre.hxx:1047
bool CheckStringResultLen(OUString &rResult, sal_Int32 nIncrease)
Definition: interpre.hxx:1108
void SetTab(SCTAB nTabP)
Definition: address.hxx:295
formula::FormulaConstTokenRef PopToken()
Definition: interpr4.cxx:765
ScMatValType GetDoubleOrStringFromMatrix(double &rDouble, svl::SharedString &rString)
Definition: interpr4.cxx:2423
int i
void PushDouble(double nVal)
Definition: interpr4.cxx:1789
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:1043
bool MustHaveParamCountMin(short nAct, short nMin)
Definition: interpre.hxx:1069
void PushMatrix(const sc::RangeMatrix &rMat)
Definition: interpr4.cxx:1890
svExternalDoubleRef
sal_uInt16 GetDay() const
void GetCellString(svl::SharedString &rStr, ScRefCellValue &rCell)
Definition: interpr4.cxx:244
svExternalSingleRef
FormulaError nGlobalError
Definition: interpre.hxx:198
void GetVars(SCCOL &nCol1, SCROW &nRow1, SCTAB &nTab1, SCCOL &nCol2, SCROW &nRow2, SCTAB &nTab2) const
Definition: address.hxx:690
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:1966
double uniform_real_distribution(double a=0.0, double b=1.0)
svSingleRef
#define Y
FormulaError
sal_uInt8 GetByte() const
Definition: interpre.hxx:415
svDoubleRef
void PushIllegalArgument()
Definition: interpr4.cxx:1931
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:186
svMatrix
int GetError()
svl::SharedString GetString()
Definition: interpr4.cxx:2322
void PushNoValue()
Definition: interpr4.cxx:1941
void PopError()
Definition: interpr4.cxx:753
#define SAL_WARN(area, stream)
double get() const
Returns the final sum.
Definition: kahan.hxx:211
void PushTokenRef(const formula::FormulaConstTokenRef &)
Pushes the token or substitutes with formula::FormulaErrorToken in case nGlobalError is set and the t...
Definition: interpr4.cxx:666
OUString getString(const Any &_rAny)
svMissing
void SetError(FormulaError nError)
Definition: interpre.hxx:1007
aStr
bool GetBool()
Definition: interpre.hxx:433
static bool lcl_SortByX(const DataPoint &lhs, const DataPoint &rhs)
Definition: interpr8.cxx:36
B2DRange maRange
sal_Int16 SCTAB
Definition: types.hxx:22
bool m_bDetectedRangeSegmentation false