Background Color Coding for a Calculated Dimension in a Pivot Chart

[ZoeM]

Community.

I am puzzled by my situation. 
I have a calculated dimension and I want the background colored based on the result of that calculated dimension. 

But it is only coloring a few of the available results in the table. 

My calculated dimension expression is:

=Aggr( Concat( {$<[Date Type]={'Start Dates'},Reference_Only={'No'}, [Declaration Status]={'Next'},Year=>} DISTINCT Rating),[MY Program])

My expression background color expression is:

=If(Aggr( Concat( {$<[Date Type]={'Start Dates'},Reference_Only={'No'}, [Declaration Status]={'Next'},Year=>} DISTINCT Rating),[MY Program])='G',RGB(146,208,80),

If(Aggr( Concat( {$<[Date Type]={'Start Dates'},Reference_Only={'No'}, [Declaration Status]={'Next'},Year=>} DISTINCT Rating),[MY Program])='Y',RGB(255,255,0),

If(Aggr( Concat( {$<[Date Type]={'Start Dates'},Reference_Only={'No'}, [Declaration Status]={'Next'},Year=>} DISTINCT Rating),[MY Program])='R',RGB(255,0,0))))

Has anybody else experienced this and what have you found as a solution?

Thanks. 

[sunny_talwar]

Do you get a single value of rating on each row or do you sometimes have multiple values as well? like R,G? 

[ZoeM]

The possible values are G, R/G, NR, Y/G, Y, R, G/Y, R/Y, Y/R, G/R

[sunny_talwar]

What would you want R/G to be colored as?

[ZoeM]

I don't think we can do gradients in Qlik Pivot Charts, so the combination colors can be the lower case color. So R/G would be Red, Y/G would be Yellow etc. 

Mainly I really just want to understand why the others are not being colored lol. 

[sunny_talwar]

What exactly do you mean by Others?

[ZoeM]

 when I set it up and added the expression to the background color portion of the Calculated Dimension,  in the results it only colors three items and leaves the other blank. 

I have multiple 'G' results and it will only color 1 and same for 'R' and 'Y'. 

[sunny_talwar]

Can you try this

=If(Aggr(NODISTINCT Concat( {$<[Date Type]={'Start Dates'},Reference_Only={'No'}, [Declaration Status]={'Next'},Year=>} DISTINCT Rating),[MY Program])='G',RGB(146,208,80),

If(Aggr(NODISTINCT Concat( {$<[Date Type]={'Start Dates'},Reference_Only={'No'}, [Declaration Status]={'Next'},Year=>} DISTINCT Rating),[MY Program])='Y',RGB(255,255,0),

If(Aggr(NODISTINCT Concat( {$<[Date Type]={'Start Dates'},Reference_Only={'No'}, [Declaration Status]={'Next'},Year=>} DISTINCT Rating),[MY Program])='R',RGB(255,0,0))))

[ZoeM]

 So it colored a few more but not all! I am getting closer! Is it a glitch??

[sunny_talwar]

May be share a sample so that we can check it out.