Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
sudip234
Contributor
Contributor

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

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.

Capture.PNG

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

Any help would be extremely useful!!!

Thanks


3 Replies
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)))

sudip234
Contributor
Contributor
Author

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