Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
AFAIK, sum doesn't work with Column()
use aggr function
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.