Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
ZoeM
Specialist
Specialist

Background Color Coding for a Calculated Dimension in a Pivot Chart

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. 

2 Solutions

Accepted Solutions
sunny_talwar

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())))

View solution in original post

sunny_talwar

31 Replies
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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

 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
Specialist
Specialist
Author

 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.