Trendline

Trendline is an additional line that indicates the slope (or trend) in a particular data series.

<p>Trendline is an additional line that indicates the slope (or trend) in a particular data series and is also known as a line of best fit. Trend lines can be helpful when you are analysing data because they can forecast future values based upon your current data.</p> <p>Users can create 6 different types of trendlines for their charts: including linear, exponential, logarithmic, polynomial, power, and moving average.</p> <ul> <li><strong>Linear</strong>: a best fit straight line for simple linear data sets.</li> <li><strong>Exponential</strong>: a best-fit curved line that illustrates how data values increase or decrease and then level out.</li> <li><strong>Logarithmic</strong>: a best-fit curved line that illustrates the data increases or decreases quickly and then levels out.</li> <li><strong>Polynomial</strong>: a curved line illustrating fluctuations in the data values based on the order property.</li> <li><strong>Power</strong>: a curved line to compare measurements that increase at a specific rate.</li> <li><strong>MovingAverage</strong>: averages a specific number of data points, and uses the value as a point in the line.</li> </ul> <p>Trendline supports the following chart types:</p> <ul> <li><strong>Column</strong></li> <li><strong>Bar</strong></li> <li><strong>Line</strong></li> <li><strong>Scatter</strong></li> <li><strong>Area</strong></li> </ul> <p>You can create a linear trendline using the following code:</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> series = chart.series().get(<span class="hljs-number">0</span>); series.trendlines = [ { <span class="hljs-attr">type</span>: GC.Spread.Sheets.Charts.TrendlineType.linear, }, ]; chart.series().set(<span class="hljs-number">0</span>, series); </code></pre> <h2 id="customize">Customize</h2> <p><strong>order</strong>: Specify the number of terms in the <strong>Polynomial</strong> equation. The order is a integer with range 2 to 6.</p> <p><strong>intercept</strong>: Specify the intercept for <strong>linear</strong>, <strong>exponential</strong>, <strong>polynomial</strong> trendline.</p> <p><strong>displayEquation & displayRSquared</strong>: Specify whether to use equation or R squared for the trendline.</p> <p><strong>forward & backward</strong>: use forward or backward to project the data. </p> <p>The displayEquation, displayRSquared, forward, backward supports <strong>linear</strong>, <strong>exponential</strong>, <strong>logarithmic</strong>, <strong>polynomial</strong>, <strong>power</strong> trendline.</p> <p><strong>style</strong>: Specify the line style of the trendline, including color, width, and dashline.</p> <p><strong>name</strong>: Specify the name of the trendline. The built-in name will be used if no name is provided.</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> series = chart.series().get(<span class="hljs-number">0</span>); series.trendlines = [ { <span class="hljs-attr">type</span>: GC.Spread.Sheets.Charts.TrendlineType.polynomial, <span class="hljs-attr">order</span>: <span class="hljs-number">4</span>, <span class="hljs-attr">intercept</span>: <span class="hljs-number">0</span>, <span class="hljs-attr">displayEquation</span>: <span class="hljs-literal">true</span>, <span class="hljs-attr">displayRSquared</span>: <span class="hljs-literal">true</span>, <span class="hljs-attr">forward</span>: <span class="hljs-number">3</span>, <span class="hljs-attr">name</span>: <span class="hljs-string">"Customized Polynomial Trendline"</span>, <span class="hljs-attr">style</span>: { <span class="hljs-attr">color</span>: <span class="hljs-string">'red'</span>, <span class="hljs-attr">width</span>: <span class="hljs-number">2</span>, <span class="hljs-attr">dashStyle</span>: GC.Spread.Sheets.Charts.LineDashStyle.dash } } ]; chart.series().set(<span class="hljs-number">0</span>, series); </code></pre> <p><strong>Period</strong>: Specify the period of the <strong>MovingAverage Trendline</strong>. The order is a integer with range 2 to data set count minus 1.</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> series = chart.series().get(<span class="hljs-number">0</span>); series.trendlines = [ { <span class="hljs-attr">type</span>: GC.Spread.Sheets.Charts.TrendlineType.movingAverage, <span class="hljs-attr">Period</span>: <span class="hljs-number">3</span> } ]; chart.series().set(<span class="hljs-number">0</span>, series); </code></pre>
var advData = [ ['Advertising', 'Items sold'], [28, 17], [34, 19], [41, 18], [47, 20], [52, 24], [59, 26], [65, 29], [72, 31], [80, 34], [87, 39], [94, 40], [102, 42], ]; var salesData = [ ['', 'Sales'], ['Jan', 54], ['Feb', 60], ['Mar', 86], ['Apr', 92], ['May', 112], ['Jun', 157], ['Jul', 202], ['Aug', 195], ['Sep', 187], ['Oct', 194], ['Nov', 238], ['Dec', 289], ]; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSheet1(spread); initSheet2(spread); }; function initSheet1(spread) { var sheet1 = spread.getSheet(0); sheet1.name('Basic'); sheet1.setArray(0, 0, advData); sheet1.setArray(18, 0, salesData); for (var i = 0; i < 12; i ++) { sheet1.getCell(i + 1, 0).formatter('$#,##0'); } // Choose a suitable trendline type from GC.Spread.Sheets.Charts.TrendlineType to fit your chart var chart1 = sheet1.charts.add("chart1", GC.Spread.Sheets.Charts.ChartType.xyScatter, 130, 5, 500, 350, "A1:B13", GC.Spread.Sheets.Charts.RowCol.columns); var axes = chart1.axes(); axes.primaryValue.title.text = 'Items sold'; axes.primaryCategory.title.text = 'Advertising'; axes.primaryCategory.majorGridLine.visible = true; axes.primaryCategory.majorUnit = 10; chart1.axes(axes); var targetSeriesIndex = 0; var targetSeries = chart1.series().get(targetSeriesIndex); var linearTrendline = { type: GC.Spread.Sheets.Charts.TrendlineType.linear, style: { color: 'red', width: 2 } }; targetSeries.trendlines = [ linearTrendline ]; chart1.series().set(targetSeriesIndex, targetSeries); var chart2 = sheet1.charts.add("chart2", GC.Spread.Sheets.Charts.ChartType.columnClustered, 130, 360, 500, 350, "A19:B31", GC.Spread.Sheets.Charts.RowCol.columns); var targetSeriesIndex = 0; var targetSeries = chart2.series().get(targetSeriesIndex); var exponentialTrendline = { type: GC.Spread.Sheets.Charts.TrendlineType.exponential, style: { color: 'orange', width: 2, dashStyle: GC.Spread.Sheets.Charts.LineDashStyle.dash } }; targetSeries.trendlines = [ exponentialTrendline ]; chart2.series().set(targetSeriesIndex, targetSeries); } function initSheet2(spread) { // More settings var sheet2 = spread.getSheet(1); sheet2.name('Advance'); sheet2.setArray(0, 0, advData); sheet2.setArray(18, 0, salesData); for (var i = 0; i < 12; i ++) { sheet2.getCell(i + 1, 0).formatter('$#,##0'); } // Change the order(the highest power for the independent variable) of polynomial trendline to adjust R-squared value // Also you could show the equation and R-squared value in chart area if you want var chart3 = sheet2.charts.add("chart3", GC.Spread.Sheets.Charts.ChartType.xyScatter, 130, 5, 500, 350, "A1:B13", GC.Spread.Sheets.Charts.RowCol.columns); var axes = chart3.axes(); axes.primaryValue.title.text = 'Items sold'; axes.primaryCategory.title.text = 'Advertising'; axes.primaryCategory.majorGridLine.visible = true; axes.primaryCategory.majorUnit = 10; chart3.axes(axes); var targetSeriesIndex = 0; var targetSeries = chart3.series().get(targetSeriesIndex); var polynomialTrendline = { type: GC.Spread.Sheets.Charts.TrendlineType.polynomial, order: 4, displayEquation: true, displayRSquared: true, style: { color: 'red', width: 2 } }; targetSeries.trendlines = [ polynomialTrendline ]; chart3.series().set(targetSeriesIndex, targetSeries); // Set a value in the Forward and Backward fields to project your data into the future. var chart4 = sheet2.charts.add("chart4", GC.Spread.Sheets.Charts.ChartType.columnClustered, 130, 360, 500, 350, "A19:B31", GC.Spread.Sheets.Charts.RowCol.columns); var targetSeriesIndex = 0; var targetSeries = chart4.series().get(targetSeriesIndex); var exponentialTrendline = { type: GC.Spread.Sheets.Charts.TrendlineType.exponential, forward: 3, style: { color: 'orange', width: 2, dashStyle: GC.Spread.Sheets.Charts.LineDashStyle.dash } }; targetSeries.trendlines = [ exponentialTrendline ]; chart4.series().set(targetSeriesIndex, targetSeries); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets-charts/dist/gc.spread.sheets.charts.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: 100% ; height: 100%; overflow: hidden; float: left; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }