Lollipop Variance Sparkline

Lollipop variance sparkline is a powerful data visualization technique that can be used to show absolute and relative variances in a dataset. SpreadJS allows you to create lollipop variance sparklines by using the LOLLIPOPVARISPARKLINE method.

A lollipop variance sparkline conveys the same information as a bar chart, but it reduces display space by turning rectangles into lines, emphasizing data points for a simpler display. Thus, Lollipop charts are more aesthetically pleasing when dealing with large volumes of data.

lollipop-sparkline-labels

The above image shows a Lollipop Variance Sparkline and its elements:

Element	Description
Reference	All values are offset relative to this reference value.
lollipop	Lollipop length represents variance size.
mini	Represents the minimum that the lollipop sparkline can paint.
maxi	Represents the maximum that the lollipop sparkline can paint.
label	Represents the accurate number of the data.
arrow	Display only when the value is greater than maximum or smaller than minimum. The appearance of arrow is dependent on whether the current variance is positive or negative and displayed as vertical or horizontal. For Horizontal: If variance is positive: Arrow is shown at the right of the cell. If variance is negative: Arrow is shown at the left of the cell. For Vertical: If variance is positive: Arrow is shown at the top of the cell. If variance is negative: Arrow is shown at the bottom of the cell.
tick	Base unit that a user can set.
colorPositive	Represents that the lollipop bar fills color when the variance value is bigger than the reference.
colorNegative	Represents that the lollipop bar fills color when the variance value is smaller than the reference.

The Lollipop Variance sparkline formula has the following options:

Arguments	Type	Default Value	Description
plannedValue [required]	number or reference		Specifies the expected data set to be passed in the whole range of the planned value area.
actualValue [required]	number or reference		Specifies the actual data set passed in the whole range of the actual value area.
index [required]	number or reference		Specifies 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. It starts from 0.
absolute [optional]	boolean	false	Specifies whether the lollipop mini graph shows absolute variance or relative variance. false- the variance values don’t affect each other. true: the variance is drawn strictly in proportion, which means they affect each other, and the reference, maxi, and mini parameters do not work.
reference [optional]	number or reference	0	Specifies the location of the reference line.
mini [optional]	number or reference	-1	Specifies the minimum value of the sparkline.
maxi [optional]	number or reference	1	Specifies the maximum value of the sparkline.
tickunit [optional]	number or reference	0	Specifies tick unit.
legend [optional]	boolean	FALSE	Specifies whether it shows data label.
colorPositive [optional]	string	“green“	Sets the color when the variance is bigger than the reference.
colorNegative [optional]	string	“red”	Sets the color when the variance is smaller than the reference.
lollipopHeaderColor [optional]	string	“black“	Sets the lollipop header fill color by this parameter.
vertical [optional]	boolean	FALSE	Specifies whether to display the sparkline vertically or not.

The Lollipop Variance sparkline formula has the following syntax:

=LOLLIPOPVARISPARKLINE(plannedValue,actualValue,index,absolute,reference,mini,maxi,tickunit,legend,colorPositive,colorNegative,lollipopHeaderColor,vertical);

Usage Scenario

Consider a scenario where an organization wants to create an economic data report to help them analyze the profit variance as a percentage of the planned profit of different districts, reflecting the gap between reality and expectations.

The lollipop variance sparkline also displays an arrow in the chart when the value is greater than maximum or smaller than minimum and the data exceeds your expectations.

lollipop-sparkline

     var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
     var sheet = spread.getActiveSheet();
     sheet.name('Lollipop variance sparkline');
     sheet.setRowCount(20);
     sheet.setColumnCount(40);
     sheet.suspendPaint();
     sheet.addSpan(0, 0, 1, 5);
     sheet.setValue(0, 0, "Regional Sales Data Analysis Statistics Table");
     sheet.getCell(0, 0).backColor("#196a99");
     sheet.setArray(1, 0, [
         ["District", "Sales target", "Actual sales", "Variance", "Variance %"],
         ["Asia", 4500, 3500],
         ["Europe", 3758, 3920],
         ["NorthAmerica", 4525, 5002],
         ["SouthAmerica", 3200, 3480],
         ["Oceania", 1540, 1800],
         ["Africa", 430, 430],
     ]);
     for (let i = 2; i < 8; i++) {
         sheet.setFormula(i, 3, '=LOLLIPOPVARISPARKLINE($B$3:$B$8,$C$3:$C$8,' + (i - 2) + ',true,,,,200,true,"#cecece","#cecece","#ffa501")');
         sheet.setFormula(i, 4, '=LOLLIPOPVARISPARKLINE($B$3:$B$8,$C$3:$C$8,' + (i - 2) + ',false,0,-0.2,0.2,0.1,true,"green","red","skyblue")');

     }
     widthList = [100, 100, 100, 300, 300];
     for (let i = 0; i < widthList.length; i++) {
         sheet.setColumnWidth(i, widthList[i]);
     }
     heightList = [50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50];
     for (let i = 0; i < heightList.length; i++) {
         sheet.setRowHeight(i, heightList[i]);
     }
     sheet.getRange('A1:E13').hAlign(1).vAlign(1);
     sheet.getRange('A1:E1').font('bold normal 15px normal');
     sheet.getRange('A1:A13').font('bold normal 15px normal');
     sheet.getRange(2, 3, 6, 1).setBorder(new GC.Spread.Sheets.LineBorder("#fff", GC.Spread.Sheets.LineStyle.empty), {
         inside: true
     });
     sheet.resumePaint();

Using Designer

You can set the lollipop variance sparkline using the Spread Designer by selecting the Lollipop Variance Sparkline button available inside the INSERT > Sparklines tab group.

lollipopsparkline-designertab

You can edit the function parameters using the Lollipop Variance Sparkline Setting dialog.

lollipopvariance-designer