I want to create a pivot table with a hierarchal layout. The rows would be site and then “site1”, columns are “cr” with different categories and the values would be field “nid”. I want three different conditional format backgrounds,
blue if there is a value.
grey, if I mark the matrix intersection as grey (ie cr = cars AND site1= Chile, should return grey background)
orange if it is empty.
The issue is that my dataset does not have null values, the cells simply don’t exists so conditional formatting doesn’t work does not affect these empty cells. Regular heat map is insufficient and unclear for this purpose.
How would I go about creating a HighChart using these parameters?
What about implementing the following workaround with the built-in capabilities of Quick Sight?
Create a regular pivot table for which you set (within its properties) the “Cells” background color to orange. This is to color all the empty cells orange.
Next, you add a new calculated field named background_color. Pick now a mapping between the desired colors and a unique number. Let’s say we chose
1 for grey
2 for blue
Based on that mapping you can define your calculated field in a way similar to:
avg(ifelse(cr='cars' AND site1='Chile',1,ifelse(isNotNull(nid),2,0)))
Next, you go to the “Conditional formatting” settings in the visual’s properties, select column ‘nid’, and choose “Add background color”. Within the next screen, you are selecting the background_color field as the field to base the format on. and add one condition for each color mapping that you need. In your case, one for grey (value equals 1) and one for blue (value equals 2). The final configuration should then look as follows:
Using the approach described above, I colored the cell for Segment=“SMB” AND License=“ZZU7U59ZX9” in grey, all cells with a value in blue and all empty cells in orange.
Hi @Roger123,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.
