Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So I am trying to add in colors to my pivot table, which works perfectly. However when I do add in these color expressions the pivot table will show non existing values.
In the pictures below you will see an example of before and after the background color expression.
Before:
After:
As you can see with the color coding there are 0's being added in for values that do not exist.
This is the code that I use:
if("year"='2011' and [Band Unified]='ALL', rgb(255, 0, 0),//Red
if("year"='2011' and [Band Unified]='VII', rgb(255, 0, 0),//Red
if("year"='2011' and [Band Unified]='VI', rgb(255, 0, 0),//Red
if("year"='2011' and [Band Unified]='V', rgb(255, 192, 0),//Orange
if("year"='2011' and [Band Unified]='IV', rgb(146, 208, 80),//Green
if("year"='2011' and [Band Unified]='III', rgb(91, 155, 213),//Blue
if("year"='2012' and [Band Unified]='ALL', rgb(255, 0, 0),//Red
if("year"='2012' and [Band Unified]='VII', rgb(255, 0, 0),//Red
if("year"='2012' and [Band Unified]='VI', rgb(255, 0, 0),//Red
if("year"='2012' and [Band Unified]='V', rgb(255, 192, 0),//Orange
if("year"='2012' and [Band Unified]='IV', rgb(146, 208, 80),//Green
if("year"='2012' and [Band Unified]='III', rgb(91, 155, 213),//Blue
if("year"='2013' and [Band Unified]='ALL', rgb(255, 0, 0),//Red
if("year"='2013' and [Band Unified]='VII', rgb(255, 0, 0),//Red
if("year"='2013' and [Band Unified]='VI', rgb(255, 0, 0),//Red
if("year"='2013' and [Band Unified]='V', rgb(146, 208, 80),//Green
if("year"='2013' and [Band Unified]='IV', rgb(146, 208, 80),//Green
if("year"='2013' and [Band Unified]='III', rgb(91, 155, 213),//Blue
if("year"='2014' and [Band Unified]='ALL', rgb(255, 0, 0),//Red
if("year"='2014' and [Band Unified]='VII', rgb(255, 0, 0),//Red
if("year"='2014' and [Band Unified]='VI', rgb(255, 192, 0),//Orange
if("year"='2014' and [Band Unified]='V', rgb(146, 208, 80),//Green
if("year"='2014' and [Band Unified]='IV', rgb(91, 155, 213),//Blue
if("year"='2014' and [Band Unified]='III', rgb(91, 155, 213),//Blue
if("year"='2015' and [Band Unified]='ALL', rgb(255, 0, 0),//Red
if("year"='2015' and [Band Unified]='VII', rgb(255, 0, 0),//Red
if("year"='2015' and [Band Unified]='VI', rgb(255, 192, 0),//Orange
if("year"='2015' and [Band Unified]='V', rgb(146, 208, 80),//Green
if("year"='2015' and [Band Unified]='IV', rgb(91, 155, 213),//Blue
if("year"='2015' and [Band Unified]='III', rgb(91, 155, 213),//Blue
if("year"='2016' and [Band Unified]='ALL', rgb(255, 0, 0),//Red
if("year"='2016' and [Band Unified]='VII', rgb(255, 192, 0),//Orange
if("year"='2016' and [Band Unified]='VI', rgb(146, 208, 80),//Green
if("year"='2016' and [Band Unified]='V', rgb(91, 155, 213),//Blue
if("year"='2016' and [Band Unified]='IV', rgb(91, 155, 213),//Blue
if("year"='2016' and [Band Unified]='III', rgb(91, 155, 213),//Blue
if("year"='2017' and [Band Unified]='ALL', rgb(255, 0, 0),//Red
if("year"='2017' and [Band Unified]='VII', rgb(255, 192, 0),//Orange
if("year"='2017' and [Band Unified]='VI', rgb(146, 208, 80),//Green
if("year"='2017' and [Band Unified]='V', rgb(91, 155, 213),//Blue
if("year"='2017' and [Band Unified]='IV', rgb(91, 155, 213),//Blue
if("year"='2017' and [Band Unified]='III', rgb(91, 155, 213),//Blue
if("year"='2018' and [Band Unified]='ALL', rgb(255, 192, 0),//Orange
if("year"='2018' and [Band Unified]='VII', rgb(146, 208, 80),//Green
if("year"='2018' and [Band Unified]='VI', rgb(91, 155, 213),//Blue
if("year"='2018' and [Band Unified]='V', rgb(91, 155, 213),//Blue
if("year"='2018' and [Band Unified]='IV', rgb(91, 155, 213),//Blue
if("year"='2018' and [Band Unified]='III', rgb(91, 155, 213)//Blue
))))))))))))))))))))))))))))))))))))))))))))))))
Does anyone have an idea of what is going wrong?
Next to that I am also wondering if it is possible to give a color value to empty cells, so if year would be 2018 and Band column would be III, then the empty cell should also color blue.
Cheers