Unable to use Color by Expression with a custom dimension

[BZ]

Hello folks,

I have built myself a custom dimension called "Batch" that I use for tables and charts that looks like this:

=IF(BatchQuarter <= -3, '1. ECO',
    IF(BatchQuarter >= -2 AND BatchQuarter <=-1, '2. Carryover',
    	IF(BatchQuarter = 0, '3. Current', 
       	    IF(BatchQuarter > 0, '4. Future')
        	)
          )
  )

Now I would like to apply custom colors to it depending on the Batch. I've tried to accomplish this thusly:

=IF(BatchQuarter <= -3, black(),
    IF(BatchQuarter >= -2 AND BatchQuarter <=-1, blue(),
    	IF(BatchQuarter = 0, green(), 
       	    IF(BatchQuarter > 0, yellow())
        	)
          )
  )

Unfortunately, this only seems to affect the "Current" batch - all the rest remain light grey.

I also tried by first creating a Master Dimension with the first code, but then I'm not able to use the Master Dimension in the color expression...

Could you help me sort this out, please?

Thanks!

[sunny_talwar]

Does this happen based on selection only? Or is this with or without selections?

[BZ]

There are no selections or filters in place, if that's what you're asking.

[sunny_talwar]

Would you be able to share a sample where we can see the issue?

[BZ]

Here's a an app with sample data and the calculations I described above. Does it help?

[sunny_talwar]

May be create a master dimension and assign the colors within the master dimension's configuration

[BZ]

Thanks. Unfortunately this option is not available in the version we have.

At any rate, it's a workaround, but doesn't explain why an otherwise perfect color expression doesn't work.

[Patrik_Lundblad · Accepted solution]

Hi,

The issue is that you have multiple values (BatchQuarters) for each slice, thus it can not identify one color to use, despite the same color being calculated multiple times per slice it's not one color. So the only slices that get their colors is the green and yellow one as they only have one BatchQuarter value. 

Here is an example using a table with and without the BathQuarter as a field.

What we need to do then is to calculate one value per slice. I tried adding min and max to the expression and it seems to work. But could be good if you thought through what other type of values you may have and adjusted the expression.

=IF(Max(BatchQuarter) <= -3, black(),
    IF(Min(BatchQuarter) >= -2 AND Max(BatchQuarter) <=-1, blue(),
    	IF(Min(BatchQuarter) = 0, green(), 
       	    IF(Min(BatchQuarter) > 0, yellow())
        	)
          )
  )

Which looks like this

Cheers,

Patrik.