Comparing four values for a cell background color

[Francine]

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

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

[chrismarlow]

Hi,

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

Cheers,

Chris.

[Francine]

Hi Chris,

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

Francine

[chrismarlow]

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;

& 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]

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

[Francine]

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]

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.