Color expression not functions within buckets created by aggr function in qlikview

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

I need to create buckets based on the % of target achieved and then count the # of accounts falling in each bucket.

I'm using a calculated dimension for the purpose of bucketing.

The first dimension looks something like this

=if(Aggr(sum(Units)/sum(Target),[Head Office],Brand,QuarterYear,GROUP)<0.75,dual('<75%',1),

if(Aggr(sum(Units)/sum(Target),[Head Office],Brand,QuarterYear,GROUP)<0.8,DUAL('75%-80%',2),

if(Aggr(sum(Units)/sum(Target),[Head Office],Brand,QuarterYear,GROUP)<0.85,DUAL('80%-85%',3),

if(Aggr(sum(Units)/sum(Target),[Head Office],Brand,QuarterYear,GROUP)<0.9,Dual('85%-90%',4),

if(Aggr(sum(Units)/sum(Target),[Head Office],Brand,QuarterYear,GROUP)<0.95,dual('90%-95%',5),

if(Aggr(sum(Units)/sum(Target),[Head Office],Brand,QuarterYear,GROUP)<1.0,dual('95%-100%',6),

if(Aggr(sum(Units)/sum(Target),[Head Office],Brand,QuarterYear,GROUP)<1.05,dual('100%-105%',7),

if(Aggr(sum(Units)/sum(Target),[Head Office],Brand,QuarterYear,GROUP)<1.1,dual('105%-110%',8),

if(Aggr(sum(Units)/sum(Target),[Head Office],Brand,QuarterYear,GROUP)<1.15,dual('110%-115%',9),

if(Aggr(sum(Units)/sum(Target),[Head Office],Brand,QuarterYear,GROUP)<1.2,dual('115%-120%',10),

if(Aggr(sum(Units)/sum(Target),[Head Office],Brand,QuarterYear,GROUP)<1.25,dual('120%-125%',11),

if(Aggr(sum(Units)/sum(Target),[Head Office],Brand,QuarterYear,GROUP)<1.3,dual('125%-130%',12),

if(Aggr(sum(Units)/sum(Target),[Head Office],Brand,QuarterYear,GROUP)<1.35,dual('130%-135%',13),

dual('>135%',14))))))))))))))

and the second dimension is GROUP

The Expression uses:

Count(distinct Shop ID)

This is working fine.

I now would like to highlight the borderline cases where a little increase in the target achieved can get the shop higher discounts.

The Background color code i'm using is:

=if(match(if(Aggr(sum(Units)/sum(Target),[Head Office],Brand,QuarterYear,GROUP)<0.75,dual('<75%',1),