You can create a Lollipop Variance sparkline using the LollipopVariSparkline function in a formula: =LOLLIPOPVARISPARKLINE(plannedValue, actualValue ,index ,absolute?,reference?, mini?, maxi?, tickunit?, legend?, colorPositive?, colorNegative?, lollipopHeaderColor?, vertical?).
The function has the following parameters:
plannedValue: [number or reference] that represents the Expected data set, you should pass in the whole range of the planned value area, such as “B2:B8“ or "{0;1;2;3;4;5;6}".
actualValue: [number or reference] that represents the Actual data set, you should pass in the whole range of the actual value area, such as “C2:C8“ or "{0;1;2;3;4;5;6}".
index: [number or reference] that represents the data index of the plannedValue and the actualValue.For horizontal plots, this is related to the row index, and for vertical plots, this is related to the column index. The index starts from 0.
absolute: (optional) [boolean] that determines whether the lollipop mini graph shows absolute variance or relative variance.
reference: (optional) [number or reference] that represents the location of the reference line,only work under relative variance, such as 0 or "A2"; default value is 0.
mini: (optional) [number or reference] that represents the minimum values of the sparkline, only work under relative variance, such as -5 or "A3"; default value is -1.
maxi: (optional) [number or reference] that represents the maximum values of the sparkline, only work under relative variance, such as 5 or "A4"; default value is 1.
tickunit: (optional) [number or reference] that represents the tick unit, such as 1 or "A6"; default value is 0.
legend: (optional) [boolean] that represents whether to display the text. The default value is false.
colorPositive: (optional) [string] that represents the color scheme for when the variance is larger than the reference; default value is "green".
colorNegative: (optional) [string] that represents the color scheme for when the variance is smaller than the reference; default value is "red".
lollipopHeaderColor: (optional) [string] that represents the color scheme for the lollipop header fill color; default value is "black".
vertical: (optional) [boolean] that represents whether the box's direction is vertical or horizontal; default value is false.
window.onload = function () {
var workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 });
workbook.fromJSON(IncomeStatement);
workbook.options.allowDynamicArray = true;
initSpread(workbook);
};
function initSpread(spread) {
spread.suspendPaint();
initHorizontalSheet(spread);
initVerticalSheet(spread);
spread.resumePaint();
};
function initHorizontalSheet(spread) {
let sheet = spread.getActiveSheet();
sheet.setFormula(1, 3, '=LOLLIPOPVARISPARKLINE($C$2:$C$13,$B$2:$B$13,SEQUENCE(12,1,0,1),true,0,,,0,true,"#a0ba25","#b46624","#ffa501")');
sheet.setFormula(1, 4, '=LOLLIPOPVARISPARKLINE($C$2:$C$13,$B$2:$B$13,SEQUENCE(12,1,0,1),false,0,,,0,true,"#a0ba25","#b46624","black")');
}
function initVerticalSheet(spread) {
let sheet2 = spread.getSheet(1);
sheet2.setFormula(4, 1, '=LOLLIPOPVARISPARKLINE($B$2:$I$2,$B$3:$I$3,SEQUENCE(1,8,0,1),false,0,-0.3,0.3,1000,true,"#c0c0c0","#c0c0c0","#ffa501",true)');
sheet2.setFormula(3, 1, '=LOLLIPOPVARISPARKLINE($B$2:$I$2,$B$3:$I$3,SEQUENCE(1,8,0,1),true,0,,,1000,true,"#c0c0c0","#c0c0c0","#ffa501",true)');
}
<!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/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/PlannedAndActualIncomeStatement.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 id="ss" style="width:100%;height:100%"></div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.sample-spreadsheets {
height: 100%;
width: 100%;
}
