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. 

[ZoeM]

Here is a rudimentary one outlining really how I would want this to show. 

Hope it helps. 

[sunny_talwar]

Can you pin point where we can see the issue here.... also, the expression used here is different then expression you are using... can you share how this links to your actual dashboard?

[ZoeM]

Unfortunately it would be extremely challenging to recreate this issue since I cannot share my actual data for my dashboard.

However, the reason I am using Set Analysis is because I have multiple items with multiple categories and in each one classified differently. 

Because the dashboard is selecting only a few items to color is why I am thinking that there might be a Qlik issue vs. an expression issue?

[sunny_talwar]

I am not sure how to help if I don't know the issue... May be someone else might be able to help.

[ZoeM]

Thats what I feared. 

Thanks though Sunny.

[ZoeM]

Hi Sunny,

Took me a little while, but I have attached a sample of what I would like.

In the sample, you will see that there is no color background for rating, but when you click on one of the Models, it shows up.

Any ideas?

[sunny_talwar · Accepted solution]

Try this

=If(Concat(TOTAL <Model> {$<[Start Date], Milestones={'Next'}>} DISTINCT Rating) = 'G', Green(),
	If(Concat(TOTAL <Model> {$<[Start Date], Milestones={'Next'}>} DISTINCT Rating) = 'Y', Yellow(),
	If(Concat(TOTAL <Model> {$<[Start Date], Milestones={'Next'}>} DISTINCT Rating) = 'R', Red(),
White())))

[ZoeM]

It works. 

So can you help explain what the expression is doing?

[sunny_talwar]

Instead of doing Aggr(, Model) I just used the TOTAL <Model> without Aggr because your chart already had Model, Rating as the dimensions... To tell you the truth I am not sure why Aggr(, Model) didn't work... I would have thought it to have worked also... but I recreated the same idea using TOTAL <Model>

[ZoeM]

Can we use something else vs <Total>?

Because in my dashboard  <Total> is ignoring all other filters and showing more values form the expression side of the pivot chart. .