Set Conditional Formatting Based on a Separate Measure
Introduction
The following article describes the steps needed to set conditional format on a measure based on a separate measure
Purpose/Benefits
This script allows you to format a column based on another measure value and hide this measure.
Example
In the example below, we want to format the revenue columns in red in case the margin is lower than 20%
Results:
Implementation Steps
-
Create a Column / Bar chart with the required measurements
-
Edit the widget’s script, and paste in the following JS code example:
widget.on('beforeviewloaded', function(sender, ev) {
//Define the measures participating in conditional format
var Revenue = _.find(ev.options.series,function(s){
return s.name == "Revenue";
})
var Margin = _.find(ev.options.series,function(s){
return s.name == "Margin";
})
//Set a varbiles for the measurements defined above
var r = Revenue.data;
var m = Margin.data;
for (var i = 0; i < m.length; i++) {
//define the condtions for each color
if (m[i].y< 0.2) {
r[i].color = "Red";
}
else if (m[i].y>= 0.2) {
r[i].color = "Green";
} ;
}
})
-
Script’s definition:
- Define the measures participating in the conditional format, by entering the measure name instead of <measure name>. Each measure should be define in the following format:
var Revenue = _.find(ev.options.series,function(s){
return s.name == "<measure name>";
})
-
Define a variable for each one of the measurements. Each measure should have a variable defined in the following format:
var <variable>= <measure name>.data;
-
Set the counter by one of the variables defined
for (var i = 0; i < <variable>.length; i++)
-
Set “if”statements according to the format conditions.
-
To use variable in a condition, use the following sysntax:
<variable>[i].y
-
To set a variable color, use the following sysntax:
<variable>[i].color = "Green";
-
If you wish to hide some of the measurements, use the following script
var seriesToHide = ['Margin'];
widget.on('beforeviewloaded', function (a, b) {
_.each(b.options.series, function (ser) { if (_.contains(seriesToHide, ser.name)) { ser.showInLegend = false;ser.visible = false; } });
});
widget.on('beforedatapointtooltip', function (a, b) {
var name = $$get(b, 'context.series.name') || $$get(b, 'context.pointScope.series.name');
if (_.contains(seriesToHide, name)) {
b.cancel = true;
}
});
-
Define the measures that should be hidden in the seriesToHide settings:
var seriesToHide = ['<measure1>’,'<measure1>'];
5. Save the script, refresh the widget and click apply
AdvancedCondFormat.dash
-
-
Hi All,
This can actually be easily achieved without the need for JS. This solution will work for both charts and pivots, I guess for any other widget as well.
Shout out to Itay Seroussi for this :) Thank you Eti
He describes it well in this article
Basically, within the conditional formatting formula, we will need to create a situation where the KPI upon which we want to apply the conditional formatting, on, which we will call the independent measure, will be cancelled out in the calculation.
So in the formula editor of the independent measure we will begin with multiplying the formula by the independent measure, then the threshold we would like to apply to the dependant measure, and then divide by the dependant measure, or something like this:
< (Independent Measure)*(Threshold)/(Dependant Measure) -> RED
>(Independent Measure)*(Threshold)/(Dependant Measure) -> GREEN
Let me know if you'd like any further clarification on this, feel free to reach out
Ido Darnell
2019 SISENSE PARTNER OF THE YEAR
-
Hi Ido,
That link does not work and unfortunately, the method you are describing is what I attempted to do before attempting the JS described in this post.
While the logic is sound, I continually get Elasticube errors that are vague (e.g. Unknown error occurred or a memory consumption error). I've tried to save formulas, write formulas directly in the condition, and tried it on various aggregates and I keep getting the same Elasticube errors.
Given that Sisense is so sensitive to Elasticube errors, I am more inclined to use JS.
-
Hi,
I previously placed an incomplete link, here you go:
That logic is working for me on both pivot tables and column/bar charts. The issue you're describing sounds like the formula is a bit too robust for the formula editor. Something that works may times for me when creating complex formulas is to create them step by step and starring them as staging formulas, then create the full formula by combining the staging formulas according to your required calculation. Then star the end formula as well.
Try to keep the formula as bear as possible, I would try that and test this logic again.
In any case you're more than welcome to reach out for a free consultation,
Ido
-
Hi Ido,
Thank you for sharing the link. Incidentally, this is the exact link I followed initially. My formula is the same number of steps as the one you mentioned and even less robust than the example in the link (as there are no nested formulas). Additionally, I have tried breaking it into multiple parts, both starring the formula and explicitly writing the formula into the condition. Even when I created a starred formula and included in my widget (before pulling it into the condition), I was getting the "unknown error".
The most unfortunate part is that the error message is so vague so it could be a number of things, including a bug.
Please sign in to leave a comment.
Comments
7 comments