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