0 Replies Latest reply: Apr 25, 2018 10:43 AM by Robbert Sam-Sin

# 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.