Is there a way to ignore the Total row of a column with an auto-color rule? I need to have all of the rows being compared against each other to determine the colors, but the total completely skews it.
Are there any other better options than auto-color rule to have this table compare it’s row values in a column and then apply colors based on which values are lowest --> highest, red --> green while ignoring the totals row?
This can be done by making use of the from value and to value options of the auto color rule feature on the property panel.
For example, I have a table visualization that has auto color rule applied.
When I change the from and to value to be outside the range of the grand total value, the auto color rule ignores the grand total
Alternatively, you can also make use of states which would display data differently based on the set conditions.
Please see article below for details
https://www.dundas.com/Support/learning/documentation/analyze-data/set-up-states-on-a-metric-set
Hope this helps.
Best regards
Sadly, this doesn’t help at all because the data ranges vary significantly. The end range value could be 5 or 5,000,000,000,000. I am just trying to compare the values by displaying a color heatmap kind of thing. The colors will be based on the values, it won’t be predefined.
i.e.
Table
You can make use of the distribution option in the common section and change the from/to color in the appearance section of the properties panel.
For example, by setting the distribution option to Equal Distribution and editing the from and to color, I have a visualization that appears as below.
Alternatively, you could make use of states, by setting conditions for each value range and defining the colors for each state.
Please see article below for details
https://www.dundas.com/Support/learning/documentation/analyze-data/set-up-states-on-a-metric-set
Hope this helps.
Best regards
By using the following formula I can get the weight of the row: $Value$/TOP($Value$)
This gives me a 0-1 scale of the rows value that I can then apply an auto-color rule like you showed above. This only displays the auto color rule on this formula column, though, and I want it to show on the original value column instead. Is there a way to either shift the auto color rule to another column or perhaps create an auto-color rule on the values column using data from the new formula column?
The auto color rule can be applied to any of the columns available in your table visualization from the property panel.
For example, I created a formula column and applied the auto color to the different columns in the visualization.
Apply the auto color rule to the columns.
Hope this helps.
Best regards
I don’t mean to be rude, but each of your responses seem to be to completely different questions that what was asked. The question was about applying an auto color rule to ‘Column A’ using a formula from a different column(Column B).
The only solution I can think of is to make a script to copy the column colors over from ‘Column B’ to ‘Column A’ and then hiding ‘Column B’. Doing all of that on both ‘Data changed’ and ‘Dashboard Resized’.
To achieve this, you can apply the color rule to the formula column and afterwards change the formula to return the initial column since they have the same distribution.
For example, I have two columns and used your formula for the second column $Value$/TOP($Value$)
I create the auto color rule on the formula column
Then, I edit the formula column to return the values from the Code column and it retains the auto color rule applied.
I can choose to rename the formula column and hide the Code column if necessary.
Hope this helps.
Best regards
This does not work in a scenario with dynamic data and filters at all. The column immediately recolors based off the functions return value when changed.
Edit: I created a custom script to do the whole process on my own as Dundas doesn’t seem to be able to do this easily.
The following is made using script value of “table1”. You will have to adjust it or make it more dynamic for your use cases.
The following goes in “Loading” event of dashboard.
var tag = document.createElement('script');
tag.setAttribute('type', 'text/javascript');
tag.setAttribute('src', '/Scripts/chroma.js-1.3.5/chroma.min.js');
// Add the script tag.
document.head.appendChild(tag);
//cells is jquery object or array containing cell elements from a dundas datagrid table body
window.addColumnColor = function(cells){
cells = $(cells);
var cellValues = [];
for(let x = 0; x<cells.length; x++){
//get text from cell(removing non-digits) and save value
var text = $(cells[x]).children("div").children("div").text().replace(/[^\d\.]/g,'');
cellValues.push(parseFloat(text));
}
//setup all colors and scales, goes from red to yellow to green
var colors = chroma.scale(['#f44a4b', '#fff74c', '#47e534']).mode('lrgb').colors(100);
var breaks = chroma.limits(cellValues);
var scale = chroma.scale(colors).domain(breaks);
//get new cell color and update cell
for(let x = 0; x<cells.length; x++){
var result = scale(cellValues[x]).hex();
$(cells[x]).css("background-color", result);
}
};
The following goes in “Ready” event of dashboard:
//standard mutation observer
//this is required as datagrids load rows dynamically on scroll
var MutationObserver = window.MutationObserver || window.WebKitMutationObserver || window.MozMutationObserver;
var observer = new MutationObserver(function (mutations) {
mutations.forEach(function (mutation) {
if (mutation.type === 'childList') {
//send cells of column 0 to function removing totals column with slice()
window.addColumnColor($(table1.container).find(".body-table tr").find("td[colindex='0']").slice(1));
}
});
});
$(".table1 .body-table").each(function () {
observer.observe(this, {
attributes: true,
childList: true,
characterData: true,
subtree: true
});
});
The following goes in “Data Changed” event of DataGrid:
window.addColumnColor($(table1.container).find(".body-table tr").find("td[colindex='0']").slice(1));Hi Dante,
If you want to display this sum as the total but not change this cell’s color and exclude it from the color rules, I would suggest using a different measure to display as text than the one used for the column’s colors, which I think is what you said you wanted to do earlier. You can do this using the column’s Custom Text property, which can be set to any [Measure Name]. (I.e., on the column displaying a measure without a sum total, set the Custom Text property to the measure that does include the sum total.)
If the measure used to display as colors doesn’t have a sum-aggregated total, it won’t affect the range of values assigned colors by the auto color rule. There are multiple ways you might create a measure like that:
null (means empty) as it’s automatically excluded from the color rules.We can’t really support JavaScript that modifies the table cells directly in the browser’s DOM, and it shouldn’t really be necessary, so I recommend looking at an option more like the above. Please let us know if these don’t work for you.
This is the information I was looking for. Everything seems to work except for one of my main concerns here.
Does Dundas support the following color range style? Chroma.js and Excel both support this. I have shown them in the following links.
(Sorry you can’t click the links or they break by not showing the colors in the url. Just copy and paste them into the browser.)
Good:
https://gka.github.io/palettes/#/9|s|ff0000,ffff00,008000|ffffe0,ff005e,93003a|0|0
Bad(Dundas color range):
https://gka.github.io/palettes/#/9|s|ff0000,008000|ffffe0,ff005e,93003a|0|0
I had to copy instead of click on these links for some reason, but it looks like what you want is colors from red to yellow to green instead of just between two colors? This is easier in version 7 with the new Middle Color property, but in earlier versions you can add multiple Auto Color Rules instead. For the ranges to update automatically when the data changes, this is where you may need to use a formula to calculate something like a “weight” in versions 6 and earlier. I would suggest a formula like:
($Measure$ - BOTTOM($Measure$)) / (TOP($Measure$) - BOTTOM($Measure$))
to calculate where each value is relative to the range between the top & bottom (min & max).