Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

r_sam-sin
New Contributor

Calculated dimension using If(Aggr(If)) function

Hi Guys,

Within a bar chart I am trying to add a calculated dimension by using the following formula:

=IF(Criteria='A',

Aggr(

IF(RangeSum(Above(total Sum([market]), 0, RowNo(total)))>$(vMaxSumV),Dual('Quantile 3', 3),

IF(RangeSum(Above(total Sum([market), 0, RowNo(total)))>=80-(100-$(vMaxSumV)),Dual('Quantile 1', 1),

IF(RangeSum(Above(total Sum([market]), 0, RowNo(total)))>=60-(100-$(vMaxSumV)),Dual('Quantile 2', 2),

IF(RangeSum(Above(total Sum([market]), 0, RowNo(total)))>=40,Dual('Quantile 3', 3),

IF(RangeSum(Above(total Sum([market), 0, RowNo(total)))>=20,Dual('Quantile 4', 4),

Dual('Quantile 5', 5)

))))),([sorting criteria 1], (NUM, Ascending))),

(IF(Criteria='B',

Aggr(

IF(RangeSum(Above(total Sum([market]), 0, RowNo(total)))>=$(vMaxSumLR),Dual('Quantile 3', 8),

IF(RangeSum(Above(total Sum([market]), 0, RowNo(total)))>=80-(100-$(vMaxSumLR)),Dual('Quantile 1', 6),

IF(RangeSum(Above(total Sum([market]), 0, RowNo(total)))>=60-(100-$(vMaxSumLR)),Dual('Quantile 2', 7),

IF(RangeSum(Above(total Sum([market]), 0, RowNo(total)))>=40,Dual('Quantile 3', 8),

IF(RangeSum(Above(total Sum([market]), 0, RowNo(total)))>=20,Dual('Quantile 4', 9),

Dual('Quantile 5', 10)

))))),([sorting criteria 2], (NUM, Ascending)))

,

null()

)

)

)

I want the null values to be added in quantile 3.

Thanks in advance.

Community Browser