topic Re: Calculated Dimension - defining bands in QlikView

Calculated Dimension - defining bands

Anonymous — Tue, 25 Jul 2017 17:55:05 GMT

Hi,

I am using calculated dimension in qlikview to band my customer as per their revenue. The first results looks fine but when I click on any of the bands then the selected bands is split in to other bands and should only show the selected one. I attached the screenshots for more clarification. Please let me know if I have anything wrong in my code.

Calculated Dimension:
=Aggr(IF(Sum(REVENUE)=0,Dual('0',1),
If(Sum(REVENUE)>0 and Sum(REVENUE)<=5,Dual('1-5',2),
If(Sum(REVENUE)>5 and Sum(REVENUE)<=10,Dual('6-10',3),
If(Sum(REVENUE)>10 and Sum(REVENUE)<=20,Dual('11-20',4),
If(Sum(REVENUE)>20 and Sum(REVENUE)<=50,Dual('21-50',5),
If(Sum(REVENUE)>50 and Sum(REVENUE)<=100,Dual('51-100',6),
If(Sum(REVENUE)>100 and Sum(REVENUE)<=200,Dual('101-200',7),
If(Sum(REVENUE)>200 and Sum(REVENUE)<=500,Dual('201-500',8),
If(Sum(REVENUE)>500 and Sum(REVENUE)<=1000,Dual('501-1000',9),
If(Sum(REVENUE)>1000 and Sum(REVENUE)<=2000,Dual('1001-2000',10),
If(Sum(REVENUE)>2000 and Sum(REVENUE)<=3000,Dual('2001-3000',11),
Dual('3000+',12)))))))))))),CUSTOMERNO).

The Expression : Count(distinct CUSTOMERNO)

Many Thanks in advance

Re: Calculated Dimension - defining bands

Anonymous — Tue, 25 Jul 2017 18:23:00 GMT

Thank you for your help, but same issue. While selecting the bands I am getting other bands inside.

Re: Calculated Dimension - defining bands

sunny_talwar — Tue, 25 Jul 2017 18:27:24 GMT

May be this

=Aggr(IF(Sum({<CUSTOMERNO>}REVENUE)=0,Dual('0',1),

If(Sum({<CUSTOMERNO>}REVENUE)>0 and Sum({<CUSTOMERNO>}REVENUE)<=5,Dual('1-5',2),

If(Sum({<CUSTOMERNO>}REVENUE)>5 and Sum({<CUSTOMERNO>}REVENUE)<=10,Dual('6-10',3),

If(Sum({<CUSTOMERNO>}REVENUE)>10 and Sum({<CUSTOMERNO>}REVENUE)<=20,Dual('11-20',4),

If(Sum({<CUSTOMERNO>}REVENUE)>20 and Sum({<CUSTOMERNO>}REVENUE)<=50,Dual('21-50',5),

If(Sum({<CUSTOMERNO>}REVENUE)>50 and Sum({<CUSTOMERNO>}REVENUE)<=100,Dual('51-100',6),

If(Sum({<CUSTOMERNO>}REVENUE)>100 and Sum({<CUSTOMERNO>}REVENUE)<=200,Dual('101-200',7),

If(Sum({<CUSTOMERNO>}REVENUE)>200 and Sum({<CUSTOMERNO>}REVENUE)<=500,Dual('201-500',8),

If(Sum({<CUSTOMERNO>}REVENUE)>500 and Sum({<CUSTOMERNO>}REVENUE)<=1000,Dual('501-1000',9),

If(Sum({<CUSTOMERNO>}REVENUE)>1000 and Sum({<CUSTOMERNO>}REVENUE)<=2000,Dual('1001-2000',10),

If(Sum({<CUSTOMERNO>}REVENUE)>2000 and Sum({<CUSTOMERNO>}REVENUE)<=3000,Dual('2001-3000',11),

Dual('3000+',12)))))))))))),CUSTOMERNO)

Re: Calculated Dimension - defining bands

Anonymous — Tue, 25 Jul 2017 18:39:24 GMT

Bravoo..... It is working !. Many thanks for your time.

Re: Calculated Dimension - defining bands

Anonymous — Tue, 25 Jul 2017 18:41:59 GMT

when using QlikView 12 you could also try

Aggr({<CUSTOMERNO=>} IF(Sum(REVENUE)=0,Dual('0',1),

If(Sum(REVENUE)>0 and Sum(REVENUE)<=5,Dual('1-5',2),

If(Sum(REVENUE)>5 and Sum(REVENUE)<=10,Dual('6-10',3),

If(Sum(REVENUE)>10 and Sum(REVENUE)<=20,Dual('11-20',4),

If(Sum(REVENUE)>20 and Sum(REVENUE)<=50,Dual('21-50',5),

If(Sum(REVENUE)>50 and Sum(REVENUE)<=100,Dual('51-100',6),

If(Sum(REVENUE)>100 and Sum(REVENUE)<=200,Dual('101-200',7),

If(Sum(REVENUE)>200 and Sum(REVENUE)<=500,Dual('201-500',8),

If(Sum(REVENUE)>500 and Sum(REVENUE)<=1000,Dual('501-1000',9),

If(Sum(REVENUE)>1000 and Sum(REVENUE)<=2000,Dual('1001-2000',10),

If(Sum(REVENUE)>2000 and Sum(REVENUE)<=3000,Dual('2001-3000',11),

Dual('3000+',12)))))))))))),CUSTOMERNO).

Re: Calculated Dimension - defining bands

sunny_talwar — Tue, 25 Jul 2017 18:47:29 GMT

Does syntax editor started understanding set analysis in Aggr() function or is that still a problem?

Re: Calculated Dimension - defining bands

Anonymous — Tue, 25 Jul 2017 19:20:32 GMT

Thanks Robin,

Your script gave syntax error.

Re: Calculated Dimension - defining bands

Anonymous — Wed, 26 Jul 2017 05:08:50 GMT

syntax check fails, but should work with QV 12.