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

[Anonymous]

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

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)))))))))))))),'85%-90%'),argb(50,255,255,255) bitand color(FieldIndex('GROUP', GROUP)),color(FieldIndex('GROUP’, GROUP))).

Ideally it should look like this.

But it doesn't seem to highlight the 85-90% segment.

Any help would be extremely useful!!!

Thanks

[sunny_talwar]

Why don't you just do this

=If(Aggr(Sum(Units)/Sum(Target), [Head Office], Brand, QuarterYear, GROUP) < 0.9, ARGB(50,255,255,255) bitand color(FieldIndex('GROUP', GROUP)), color(FieldIndex('GROUP’, GROUP)))

[Anonymous]

I tried this but it seems to be true in all buckets.

[sunny_talwar]

How about this

=If(

Aggr(Sum(Units)/Sum(Target), [Head Office], Brand, QuarterYear, GROUP) >= 0.85 and

Aggr(Sum(Units)/Sum(Target), [Head Office], Brand, QuarterYear, GROUP) < 0.9,

ARGB(50,255,255,255) bitand color(FieldIndex('GROUP', GROUP)), color(FieldIndex('GROUP’, GROUP)))