sc/source/ui/StatisticsDialogs/AnalysisOfVarianceDialog.cxx

   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 <sfx2/dispatch.hxx>
  12 #include <svl/zforlist.hxx>
  13 #include <svl/undo.hxx>
  14
  15 #include "formulacell.hxx"
  16 #include "rangelst.hxx"
  17 #include "scitems.hxx"
  18 #include "docsh.hxx"
  19 #include "document.hxx"
  20 #include "uiitems.hxx"
  21 #include "reffact.hxx"
  22 #include "strload.hxx"
  23 #include "random.hxx"
  24 #include "docfunc.hxx"
  25 #include "StatisticsDialogs.hrc"
  26 #include "TableFillingAndNavigationTools.hxx"
  27
  28 #include "AnalysisOfVarianceDialog.hxx"
  29
  30 namespace
  31 {
  32
  33 static sal_Int16 lclBasicStatisticsLabels[] =
  34 {
  35     STR_ANOVA_LABEL_GROUPS,
  36     STRID_CALC_COUNT,
  37     STRID_CALC_SUM,
  38     STRID_CALC_MEAN,
  39     STRID_CALC_VARIANCE,
  40     0
  41 };
  42
  43 static const char* lclBasicStatisticsFormula[] =
  44 {
  45     "=COUNT(%RANGE%)", "=SUM(%RANGE%)", "=AVERAGE(%RANGE%)", "=VAR(%RANGE%)", NULL
  46 };
  47
  48 static sal_Int16 lclAnovaLabels[] =
  49 {
  50     STR_ANOVA_LABEL_SOURCE_OF_VARIATION,
  51     STR_ANOVA_LABEL_SS,
  52     STR_ANOVA_LABEL_DF,
  53     STR_ANOVA_LABEL_MS,
  54     STR_ANOVA_LABEL_F,
  55     STR_ANOVA_LABEL_P_VALUE,
  56     STR_ANOVA_LABEL_F_CRITICAL,
  57     0
  58 };
  59
  60 static const OUString strWildcardRange("%RANGE%");
  61 static const OUString strWildcardNumber("%NUMBER%");
  62
  63 OUString lclCreateMultiParameterFormula(
  64             ScRangeList&        aRangeList, const OUString& aFormulaTemplate,
  65             const OUString&     aWildcard,  ScDocument*     pDocument,
  66             ScAddress::Details& aAddressDetails)
  67 {
  68     OUString aResult;
  69     for (size_t i = 0; i < aRangeList.size(); i++)
  70     {
  71         OUString aRangeString(aRangeList[i]->Format(SCR_ABS, pDocument, aAddressDetails));
  72         OUString aFormulaString = aFormulaTemplate.replaceAll(aWildcard, aRangeString);
  73         aResult += aFormulaString;
  74         if(i != aRangeList.size() - 1) // Not Last
  75             aResult+= ";";
  76     }
  77     return aResult;
  78 }
  79
  80 }
  81
  82 ScAnalysisOfVarianceDialog::ScAnalysisOfVarianceDialog(
  83                     SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow,
  84                     Window* pParent, ScViewData* pViewData ) :
  85     ScStatisticsInputOutputDialog(
  86             pSfxBindings, pChildWindow, pParent, pViewData,
  87             "AnalysisOfVarianceDialog", "modules/scalc/ui/analysisofvariancedialog.ui" )
  88 {
  89     get(mpAlpha, "alpha-spin");
  90 }
  91
  92 ScAnalysisOfVarianceDialog::~ScAnalysisOfVarianceDialog()
  93 {}
  94
  95 sal_Bool ScAnalysisOfVarianceDialog::Close()
  96 {
  97     return DoClose( ScAnalysisOfVarianceDialogWrapper::GetChildWindowId() );
  98 }
  99
 100 sal_Int16 ScAnalysisOfVarianceDialog::GetUndoNameId()
 101 {
 102     return STR_ANALYSIS_OF_VARIANCE_UNDO_NAME;
 103 }
 104
 105 ScRange ScAnalysisOfVarianceDialog::ApplyOutput(ScDocShell* pDocShell)
 106 {
 107     AddressWalkerWriter output(mOutputAddress, pDocShell, mDocument,
 108             formula::FormulaGrammar::mergeToGrammar( formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv));
 109     FormulaTemplate aTemplate(mDocument, mAddressDetails);
 110
 111     output.writeBoldString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ANOVA_SINGLE_FACTOR_LABEL));
 112     output.nextRow();
 113     output.nextRow();
 114
 115     // Write labels
 116     for(sal_Int32 i = 0; lclBasicStatisticsLabels[i] != 0; i++)
 117     {
 118         output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, lclBasicStatisticsLabels[i]));
 119         output.nextColumn();
 120     }
 121     output.nextRow();
 122
 123     ScRangeList aRangeList;
 124
 125     boost::scoped_ptr<DataRangeIterator> pIterator;
 126     if (mGroupedBy == BY_COLUMN)
 127         pIterator.reset(new DataRangeByColumnIterator(mInputRange));
 128     else
 129         pIterator.reset(new DataRangeByRowIterator(mInputRange));
 130
 131     // Write statistic formulas for rows/columns
 132     for( ; pIterator->hasNext(); pIterator->next() )
 133     {
 134         output.resetColumn();
 135
 136         if (mGroupedBy == BY_COLUMN)
 137             aTemplate.setTemplate(SC_STRLOAD(RID_STATISTICS_DLGS, STR_COLUMN_LABEL_TEMPLATE));
 138         else
 139             aTemplate.setTemplate(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ROW_LABEL_TEMPLATE));
 140
 141         aTemplate.applyNumber(strWildcardNumber, pIterator->index() + 1);
 142         output.writeString(aTemplate.getTemplate());
 143
 144         output.nextColumn();
 145
 146         ScRange aColumnRange = pIterator->get();
 147
 148         aRangeList.Append(aColumnRange);
 149
 150         for(sal_Int32 i = 0; lclBasicStatisticsFormula[i] != NULL; i++)
 151         {
 152             aTemplate.setTemplate(lclBasicStatisticsFormula[i]);
 153             aTemplate.applyRange(strWildcardRange, aColumnRange);
 154             output.writeFormula(aTemplate.getTemplate());
 155             output.nextColumn();
 156         }
 157         output.nextRow();
 158     }
 159
 160     output.nextRow(); // Blank row
 161
 162     // Write ANOVA labels
 163     output.resetColumn();
 164     for(sal_Int32 i = 0; lclAnovaLabels[i] != 0; i++)
 165     {
 166         output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, lclAnovaLabels[i]));
 167         output.nextColumn();
 168     }
 169     output.nextRow();
 170
 171     // Between Groups
 172     {
 173         // Label
 174         output.resetColumn();
 175         output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ANOVA_LABEL_BETWEEN_GROUPS));
 176         output.nextColumn();
 177
 178         // Sum of Squares
 179         aTemplate.setTemplate("=%TOTAL% - %WITHIN%");
 180         aTemplate.applyAddress("%TOTAL%", output.current(0, 2));
 181         aTemplate.applyAddress("%WITHIN%", output.current(0, 1));
 182         output.writeFormula(aTemplate.getTemplate());
 183         output.nextColumn();
 184
 185         // Degree of freedom
 186         aTemplate.setTemplate("=%TOTAL% - %WITHIN%");
 187         aTemplate.applyAddress("%TOTAL%", output.current(0, 2));
 188         aTemplate.applyAddress("%WITHIN%", output.current(0, 1));
 189         output.writeFormula(aTemplate.getTemplate());
 190         output.nextColumn();
 191
 192         // MS
 193         aTemplate.setTemplate("=%SS_REF% / %DF_REF%");
 194         aTemplate.applyAddress("%SS_REF%", output.current(-2, 0));
 195         aTemplate.applyAddress("%DF_REF%", output.current(-1, 0));
 196         output.writeFormula(aTemplate.getTemplate());
 197         output.nextColumn();
 198
 199         // F
 200         aTemplate.setTemplate("=%MS_BETWEEN% / %MS_WITHIN%");
 201         aTemplate.applyAddress("%MS_BETWEEN%", output.current(-1, 0));
 202         aTemplate.applyAddress("%MS_WITHIN%",  output.current(-1, 1));
 203         output.writeFormula(aTemplate.getTemplate());
 204         output.nextColumn();
 205
 206         // P-value
 207         aTemplate.setTemplate("=FDIST(%F_VAL%; %DF_BETWEEN%; %DF_WITHIN%");
 208         aTemplate.applyAddress("%F_VAL%",       output.current(-1, 0));
 209         aTemplate.applyAddress("%DF_BETWEEN%",  output.current(-3, 0));
 210         aTemplate.applyAddress("%DF_WITHIN%",   output.current(-3, 1));
 211         output.writeFormula(aTemplate.getTemplate());
 212         output.nextColumn();
 213
 214         // F critical
 215         double aAlphaValue = mpAlpha->GetValue() / 100.0;
 216         OUString aAlphaString = rtl::math::doubleToUString(
 217                                     aAlphaValue, rtl_math_StringFormat_Automatic, rtl_math_DecimalPlaces_Max,
 218                                     ScGlobal::pLocaleData->getNumDecimalSep()[0], true);
 219
 220         aTemplate.setTemplate("=FINV(%ALPHA%; %DF_BETWEEN%; %DF_WITHIN%");
 221         aTemplate.applyString("%ALPHA%",       aAlphaString);
 222         aTemplate.applyAddress("%DF_BETWEEN%", output.current(-4, 0));
 223         aTemplate.applyAddress("%DF_WITHIN%",  output.current(-4, 1));
 224         output.writeFormula(aTemplate.getTemplate());
 225     }
 226     output.nextRow();
 227
 228     // Within Groups
 229     {
 230         // Label
 231         output.resetColumn();
 232         output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ANOVA_LABEL_WITHIN_GROUPS));
 233         output.nextColumn();
 234
 235         // Sum of Squares
 236         OUString aSSPart = lclCreateMultiParameterFormula(aRangeList, OUString("DEVSQ(%RANGE%)"), strWildcardRange, mDocument, mAddressDetails);
 237         aTemplate.setTemplate("=SUM(%RANGE%)");
 238         aTemplate.applyString(strWildcardRange, aSSPart);
 239         output.writeFormula(aTemplate.getTemplate());
 240         output.nextColumn();
 241
 242         // Degree of freedom
 243         OUString aDFPart = lclCreateMultiParameterFormula(aRangeList, OUString("COUNT(%RANGE%)-1"), strWildcardRange, mDocument, mAddressDetails);
 244         aTemplate.setTemplate("=SUM(%RANGE%)");
 245         aTemplate.applyString(strWildcardRange, aDFPart);
 246         output.writeFormula(aTemplate.getTemplate());
 247         output.nextColumn();
 248
 249         // MS
 250         aTemplate.setTemplate("=%SS% / %DF%");
 251         aTemplate.applyAddress("%SS%", output.current(-2, 0));
 252         aTemplate.applyAddress("%DF%", output.current(-1, 0));
 253         output.writeFormula(aTemplate.getTemplate());
 254     }
 255     output.nextRow();
 256
 257     // Total
 258     {
 259         // Label
 260         output.resetColumn();
 261         output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ANOVA_LABEL_TOTAL));
 262         output.nextColumn();
 263
 264         // Sum of Squares
 265         aTemplate.setTemplate("=DEVSQ(%RANGE%)");
 266         aTemplate.applyRangeList(strWildcardRange, aRangeList);
 267         output.writeFormula(aTemplate.getTemplate());
 268         output.nextColumn();
 269
 270         // Degree of freedom
 271         OUString aDFPart = lclCreateMultiParameterFormula(aRangeList, "COUNT(%RANGE%)", strWildcardRange, mDocument, mAddressDetails);
 272         aTemplate.setTemplate("=SUM(%RANGE%) - 1");
 273         aTemplate.applyString(strWildcardRange, aDFPart);
 274         output.writeFormula(aTemplate.getTemplate());
 275     }
 276     output.nextRow();
 277
 278     return ScRange(output.mMinimumAddress, output.mMaximumAddress);
 279 }
 280
 281 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */