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]

I am not sure I follow... when you use this as a color expression you see issues?

[ZoeM]

I am seeing the color coding for the dimension.

However, in my actual data, I have multiple years worth of data. One item, say like BMW will have items through 2024. I have a year filter that limits it to the selected year.

I have noticed that when I used the Total, it cleared the year selection and showed the max number of years with items associated, i.e. 2024 for the BMW example. 

Im wondering if there is a way to use something else vs. Total?

[sunny_talwar]

TOTAL doesn't ignore selections.... I am not sure if what you are seeing is because of TOTAL qualifier

[ZoeM]

This is the expression for my calculated dimension that I want the background to be colored:

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

This is how I modified my background color expression:

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

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

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

I do not think it is the <Year=> syntax I added either.

So I am confounded. 

[sunny_talwar]

If you want your color expression to honor selection in Year field... why do you ignore it using Year =? I think removing that should do the trick for you

[ZoeM]

When I take <Year=> it ignores line items which have a 'Current Phase' in the following year and only considering those in the current selected year.

So in the attached QVW, Altima has a Current Phase in June 2021CY and I have 2019 selected, it will not color the rating, even though it should.

So the <Year=> context ignores the selection and shows the value coming up next. 

Please reference updated attached. 

[sunny_talwar]

I am not sure I follow what you are looking to get? Altima has Milestore of Next in 2021... if you want to color it... you need to ignore selection in Year.... but I guess if you do ignore... I am not sure what is not working correctly in that case?

[ZoeM]

Continuing with the Altima example from above, the desired end result is if the user selects 2019CY he should only see dates for 2019CY, but Altima should also show 'Road Test' as the current Phase. 

Please see attached example. 

[sunny_talwar · Accepted solution]

This?

[ZoeM]

I truly hope you work for QlikView Sunny, because you are a code breaker!

Not sure how you did it, but you've done it. 

Do you mind explaining?