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