topic Conditional Formatting for Dimension in QlikView

Conditional Formatting for Dimension

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi. I'm struggling with conditionally formatting a dimension based upon multiple criteria with my measures.

In the attached qvw, I've correctly formatted my measures that meet two criteria: 1.) the total_sales for the corresponding day has to be at or above my variable vSalesMinimum, and 2.) for the same day, the Return_Rate has to be at or above my variable vReturnRate_Threshold. Those that meet these criteria are formatted with a pinkish background.

Now for the problem: I'd like to format the background color (red) of each City_ID when the above conditions are met for a minimum of 2 days (the current value of my variable "vMinimum Days w/ Matching Criteria"). This is where I'm struggling. I've tried lots of things without much luck. I would think something like the following would work:

=if(sum(TOTAL<CITY_ID>if(column(1)>=$(vSalesMinimum) and column(2)>=$(vReturnRate_Threshold),1,0))>=$(vMinimum Days w/ Matching Critera),LightRed(),lightcyan())

But it doesn't seem to be registering the column(1) and column(2).

Any ideas?

Re: Conditional Formatting for Dimension

shiveshsingh — Sat, 24 Mar 2018 02:51:11 GMT

AFAIK, sum doesn't work with Column()

use aggr function

Re: Conditional Formatting for Dimension

Anonymous — Mon, 26 Mar 2018 16:49:52 GMT

Thanks Shivesh. Before submitting the question, I had been working with the aggr function and was only able to get it to work partially. I got the dimension to format, but with only one condition at a time (Total sales above my variable threshold or with a return rate above my variable threshold). I wasn't able to find a way to make sure both conditions where met for the same day.