Advanced Quadrant Chart

[intapiuser]

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>'
   }
 }];
 
});

[Maria_1986]

Hi there

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