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

    Calculated dimension using If(Aggr(If)) function

    Robbert Sam-Sin

      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.