topic Coloring column dynamically based on all the results for a given column in a given search (AKA Conditional Formatting) in Dashboards & Visualizations

Coloring column dynamically based on all the results for a given column in a given search (AKA Conditional Formatting)

nitzan_b — Wed, 27 May 2020 17:55:31 GMT

I’m trying to apply a color logic to a specific column in a table by range and thresholds.
I have 1000 rows in that table, with 10 rows presented in each page.
The range of colors should be the same for all the values in the table, not only those that are presented in the current page.

As suggested here I tried using the following method:

        <format type="color" field="kw_blocks / total_kw_blocks">
          <colorPalette type="list">[#DC4E41,#F8BE34,#53A051]</colorPalette>
          <scale type="threshold">33,66</scale>
        </format>
        <format type="number" field="kw_blocks / total_kw_blocks">
          <option name="unit">%</option>
        </format>

The only issue in this solution is that it uses constant thresholds:

 <scale type="threshold">33,66</scale>

However In my case I don't know in advance what will be the max value and therefore I am getting it from the query search dynamically. Therefore I would like the thresholds to be percentile of this value.
It will look something like this:

<scale type="threshold">0.33*Max(kw_blocks / total_kw_blocks),0.66*Max(kw_blocks / total_kw_blocks)</scale>

Any idea how to do it?

Re: Coloring column dynamically based on all the results for a given column in a given search (AKA Conditional Formatting)

niketn — Wed, 27 May 2020 19:59:51 GMT

@nitzan_b what is the logic that you need to apply for table cell color palette? If it is based on Range have you tried threshold based colorPalette? Following is an example:

    <format type="color" field="data">
      <colorPalette type="list">[#53A051,#B6C75A,#006D9C,#62B3B2,#F8BE34,#EC9960,#F1813F,#DC4E41]</colorPalette>
      <scale type="threshold">0,20,40,60,80,90,95</scale>
    </format>
    <format type="number" field="data">
      <option name="unit">%</option>
    </format>

If the logic is different and not covered by any of mechanism provided in Splunk Documentation: https://docs.splunk.com/Documentation/Splunk/latest/Viz/TableFormatsXML#Color_palette_types_and_options, then, please add details for the community to assist!

Re: Coloring column dynamically based on all the results for a given column in a given search (AKA Conditional Formatting)

nitzan_b — Thu, 28 May 2020 07:44:05 GMT

@niketnilay the solution you suggested is exactly what I need.
However when using threshold based colorPalette the problem is that I need to define constant thresholds:

<scale type="threshold">0,20,40,60,80,90,95</scale>

In my case I don't know in advance what will be the max value. I am getting it from the query dynamically. Therefore I would like the thresholds to be percentile of this value.
It will look something like this:

    <scale type="threshold">0.33*MaxData,0.66*MaxData</scale>

Is this doable?
I also tried to calculate these values in the query itself:

| eventstats max(data) as MaxValueColoring
| eval 33Precentile=0.33*MaxValueColoring, 66Precentile=0.66*MaxValueColoring

And then pass them as the thresholds values but this is not working either:

 <scale type="threshold">33Precentile,66Precentile</scale>