Conditional Color Formatting by Dimension / Row Combination in Pivot Table

I would like to conditionally format a pivot table depending on the combination of 2 dimensions in the view.

For example, in the screenshot below, I would like to color 'Bindable Ready to Quote' and 'cold' or 'open' green, and 'Bindable Ready to Quote' and 'pending' or 'solved' as red. Then I would like to color 'Complete Navigator' and 'cold' as green, and 'Completed Navigator' and 'open', 'pending', or 'solved' as red.

Is there a way to code in specific combinations of dimensions as colors in the view?

mmalhame

Posted 1 year ago·Last reply 1 year ago

7 comments

DRay

·1 year ago

mmalhame I want to make sure you see this reply from Jeremy.

Jeremy Friedel

·1 year ago

The Pivot 2.0 JS API, documented here, can potentially be used to create a widget script for this functionality, the cell "metadata" parameter includes information on a cell's position on a row and column level basis, once a given cell matching both a row and column conditional is found, the background color or text color of a cell can be modified using the "cell.style" parameter. This community article may be helpful for an example of this type of script.

mmalhame

OP1 year ago

Hi HamzaJ and DRay - following back up on this.

mmalhame

OP1 year ago

Thanks Hamza. Can you help me understand where I would input the HTML code? Is it using the 'Edit Script' button within the widget?

Also, I would like the background 'highlight' color within the cell to be red or green, not the font itself. How would that change the HTML code you shared?

Hamza Jap-Tjong

·1 year ago

Hey mmalhame ,

There probably is a javascript to do this, however I am not a javascript expert so my suggestion would be to use HTML.

Pivot tables allow HTML, so you could add to that field (or create a new one ) in the elasticube something like;

case

when group = 'Bindable Ready to Quote' and status in ('open','cold') then concat(concat( '<font size="6" face="verdana" color="green">',status),'</font>')

etc

Hamza

DRay

·1 year ago

Hi mmalhame,

Thank you for reaching out. Can you take a look at this post and let us know if it helps?

https://community.sisense.com/t5/knowledge-base/applying-conditional-formatting-based-on-another-measure/ta-p/303

Thank you.

mmalhame

OP1 year ago

Hi DRay - no, the post you linked does not apply because it is written about an indicator. I need to conditionally format a pivot table.