cancel
Showing results for
Did you mean:

Community Team Member

## Objective

Design a quadrant chart based on X and Y axis averages with conditional formatting.
Steps
• Create standard scatter chart
• Create a value in break by and assign the integer 1 to it
• Create Aliases for multipasses by the Point dimension for the X and Y measures
• Create 4 conditional formatting conditions all with the equal (=) operator
• Paste the formulas and swap in your own aliased functions in place of the [agg1/2 multipass]
• Save the widget (apply)
• Open it again and paste the JavaScript for the average lines
• Save the script
• Refresh the Widget (F5)
• Save the widget (apply)

## Implementation

Necessary fields

### ALIASING YOUR MULTIPASS

Set up these aliases to make function writing later on simpler.
The multipass first argument should be the Point value (set to All).
``````[agg1 multipass] <-- AVG(ContactName, [Average Revenue])

[agg2 multipass] <-- AVG(ContactName, [Average Quantity])``````

### IMPLEMENTING CODE

hardcode quarters to the integer 1.
Next, we will set the conditional formatting.
You will need 4 conditions set to equal (=).
Set the formulas to the ones below.
If you like, you can remove the lines with NULL, but I like to keep them there to keep the logic readable.
1st Formula BOTTOM RIGHT QUADRANT
``````CASE
WHEN [agg1]  < [agg1 multipass] AND [agg2] < [agg2 multipass] THEN 1
WHEN [agg1]  < [agg1 multipass] AND [agg2] > [agg2 multipass] THEN null
WHEN [agg1]  > [agg1 multipass] AND [agg2] > [agg2 multipass] THEN null
WHEN [agg1]  > [agg1 multipass] AND [agg2] < [agg2 multipass] THEN null
END``````
2nd Formula TOP LEFT QUADRANT
``````CASE
WHEN [agg1]  < [agg1 multipass] AND [agg2] < [agg2 multipass] THEN NULL
WHEN [agg1]  < [agg1 multipass] AND [agg2] > [agg2 multipass] THEN 1
WHEN [agg1]  > [agg1 multipass] AND [agg2] > [agg2 multipass] THEN null
WHEN [agg1]  > [agg1 multipass] AND [agg2] < [agg2 multipass] THEN null
END``````

3rd Formula TOP RIGHT QUADRANT
``````CASE
WHEN [agg1]  < [agg1 multipass] AND [agg2] < [agg2 multipass] THEN NULL
WHEN [agg1]  < [agg1 multipass] AND [agg2] > [agg2 multipass] THEN NULL
WHEN [agg1]  > [agg1 multipass] AND [agg2] > [agg2 multipass] THEN 1
WHEN [agg1]  > [agg1 multipass] AND [agg2] < [agg2 multipass] THEN NULL
END``````
4th Formula BOTTOM RIGHT QUADRANT
``````CASE
WHEN [agg1]  < [agg1 multipass] AND [agg2] < [agg2 multipass] THEN NULL
WHEN [agg1]  < [agg1 multipass] AND [agg2] > [agg2 multipass] THEN NULL
WHEN [agg1]  > [agg1 multipass] AND [agg2] > [agg2 multipass] THEN NULL
WHEN [agg1]  > [agg1 multipass] AND [agg2] < [agg2 multipass] THEN 1
END``````
Save the widget (apply).
Reopen the widget.

## Javascript

Paste this code the widget script.
Save the script.
Refresh the Widget (f5)
Apply the widget.
``````widget.on('processresult', function(widget, ev){
//Set Parameters
var xIsAgg = true;
var yIsAgg = true;

//Initialize Variables
var sumXValue = 0;
var sumYValue = 0;
var xValues = [];
var yValues = []
var xIndex = 0;
var yIndex = 0;

//loop through the scatter plot data, and calculate the sum of all x values, and the sum of all y values (only for numeric values)
for(var i=0; i<ev.rawResult.values.length; i++)
{
if(typeof ev.rawResult.values[i][0].data === 'number') {
sumXValue += ev.rawResult.values[i][0].data;
if(xValues.indexOf(ev.rawResult.values[i][0].data) === -1) {
xValues.push(ev.rawResult.values[i][0].data);
}
}
if(typeof ev.rawResult.values[i][1].data === 'number') {
sumYValue += ev.rawResult.values[i][1].data;
if(yValues.indexOf(ev.rawResult.values[i][1].data) === -1) {
yValues.push(ev.rawResult.values[i][1].data);
}
}
}

//Calculate the avg x and y values for the scatter. Also sort the array of xValues and yValues
var avgXValue = sumXValue / ev.rawResult.values.length;
var avgYValue = sumYValue / ev.rawResult.values.length;
xValues.sort(function(a, b){return a - b});
yValues.sort(function(a, b){return a - b});

//When using plogLines with scatter, the value isn't the actual value of x or y, instead it's the index.
//So if you want a line that is y=10, and for y, 10 is the 5th value (1,4,6,8,10,12), then for the value you need 5 instead of 10, because 10 is the 5th value in the list
//So here I need to calculate based on index of x instead of value
for(var j=0; j<xValues.length; j++) {
if(xValues[j] >= avgXValue) {
xIndex = j- ((xValues[j] - avgXValue) / (xValues[j] - xValues[j-1]));
break;
}
}

//here i'm getting the index for y instead of just using the value
for(var j=0; j<yValues.length; j++) {
if(yValues[j] >= avgYValue) {
yIndex = j- ((yValues[j] - avgYValue) / (yValues[j] - yValues[j-1]));
break;
}
}

//set the vertical avg line
ev.result.xAxis.plotLines = [ {
color: '#BEBEBE',
width: 5,
value: xIsAgg ? avgXValue : xIndex,
label: {
useHTML: true,
rotation: 0,
text: '<div>' + avgXValue.toFixed(1) + '</div>'
}
}];

//set the horizontal avg line
ev.result.yAxis[0].plotLines = [{
color: '#BEBEBE',
width: 5,
value: yIsAgg ? avgYValue : yIndex,
label: {
useHTML: true,
rotation: 0,
text: '<div>' + avgYValue.toFixed(1) + '</div>'
}
}];

});``````
7 - Data Storage

Hi there

In the conditional formatting step (Break by). could you please explain what formulas you have used for agg1 and agg2.

Version history
Last update:
‎03-02-2023 09:10 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Developers Group:

Product Feedback Forum: