Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Francine
Partner - Contributor II
Partner - Contributor II

Comparing four values for a cell background color

Good Day,

I am trying to color a cell background and am confused on how to write the expression.  The below snippet is an example of what I'd like to highlight,  The anomaly of prices between states

Francine_0-1654181449648.png

I wrote: =if(sum([Virginia])-sum([Florida])+sum([Ohio])-sum([New Hampshire]) = 0,'White', LightRed())

for each state dimension within the color cell background which works but does not provide a accurate comparison .  It highlights the entire row.  I would only like to highlight the cell that has a different value from the others on the same row.  

Thank you,

Francine

 

Labels (1)
6 Replies
chrismarlow
Specialist II
Specialist II

Hi,

This is going to be very dependent on your data structure, but the toy application attached might give some ideas.

Cheers,

Chris.

Francine
Partner - Contributor II
Partner - Contributor II
Author

Hi Chris,

Thanks but  I am not able to open that type of file 😞

Francine

 

chrismarlow
Specialist II
Specialist II

Hi,

Hmmm, is a Qlik Sense file 🤔.

Anyway code was like this;

data:
load * inline [
Car Name, State, Total
Subaru, Virgina, 1000
Subaru, Florida, 1000
Subaru, Ohio, 2000
Subaru, New Hampshire, 1000
Toyota, Virgina, 3000
Toyota, Florida, 3000
Toyota, Ohio, 3000
Toyota, New Hampshire, 2000
];

  So pivot like this;

20220602_1.png

& Background colour setting for the measure is;

If(if(HRank(Sum(Total))=1 OR HRank(Sum(Total))=Count(DISTINCT TOTAL State),1,0)=1,Red(),White())

Note similar should work in QlikView.

Cheers,

Chris.

vinieme12
Champion III
Champion III

if(substringcount(HRank(Sum(Amount)),'-')=0,LightRed(200))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Francine
Partner - Contributor II
Partner - Contributor II
Author

Thank you both.  I was hoping for an expression without having to write code because the data will constantly be  changing and this is just a sample of what of what I am trying to accomplish.  My data-set is quite large but set up on an xls in the same manor. 

chrismarlow
Specialist II
Specialist II

Hi,

I put code in as that was the code in the toy application QVF ... so you could copy and paste in to an empty application if you wanted to and recreate what I had done.

You (probably) won't have to write code ... but can't tell from what you have posted what your data is structured like.

I wonder if you are importing Excel that is structured very much like you show in your grid. That is fine, although limiting in some ways & in most applications you would manipulate so it looks more like a list Car Name, State, Amount (which you can do in Qlik - I think through the UI, so it writes code for you).

Cheers,

Chris.