topic Re: Comparing four values for a cell background color in App Development

Comparing four values for a cell background color

Francine — Thu, 02 Jun 2022 15:08:07 GMT

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

Re: Comparing four values for a cell background color

chrismarlow — Thu, 02 Jun 2022 17:26:50 GMT

Hi,

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

Cheers,

Chris.

Re: Comparing four values for a cell background color

Francine — Thu, 02 Jun 2022 17:31:50 GMT

Hi Chris,

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

Francine

Re: Comparing four values for a cell background color

chrismarlow — Thu, 02 Jun 2022 17:38:54 GMT

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.

Re: Comparing four values for a cell background color

vinieme12 — Fri, 03 Jun 2022 03:06:01 GMT

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

Re: Comparing four values for a cell background color

Francine — Fri, 03 Jun 2022 11:30:36 GMT

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.

Re: Comparing four values for a cell background color

chrismarlow — Fri, 03 Jun 2022 15:41:14 GMT

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.