Bar Chart Sorting Issue

[avinashkk696]

Hi All,

I have an issue with sorting in the bar chart.  I have a data,

LOAD * INLINE [
Client, Revenue
A, 6
B, 2
C, 1
D, 3
E, 7
F, 9
G, 9
H, 9
I, 9
J, 12
K, 11
L, 17
M, 19
N, 23
O, 44
P, 33
];

I have a calculated Dimension which has revenue ranges. 

Dimension: 

IF(Aggr(Sum(Revenue),Client)<5,'<5M',
if(Aggr(Sum(Revenue),Client)>=5 and Aggr(Sum(Revenue),Client)<10, '5M-10M',
if(Aggr(Sum(Revenue),Client)>=10 and Aggr(Sum(Revenue),Client)<15, '10M-15M',
if(Aggr(Sum(Revenue),Client)>=15 and Aggr(Sum(Revenue),Client)<20, '15M-20M',
IF(Aggr(Sum(Revenue),Client)>20 or isnull(Aggr(Sum(Revenue),Client)),'>20M',
)))))

Expression:

Sum(Revenue)

I need the dimension to sort in the order   '<5M', '5M-10M', '10M-15M',' 15M-20M', '>20M'. 

I have tried the sorting expression: 

pick(match(IF(Aggr(Sum(Revenue),Client)<5,'<5M',
if(Aggr(Sum(Revenue),Client)>=5 and Aggr(Sum(Revenue),Client)<10, '5M-10M',
if(Aggr(Sum(Revenue),Client)>=10 and Aggr(Sum(Revenue),Client)<15, '10M-15M',
if(Aggr(Sum(Revenue),Client)>=15 and Aggr(Sum(Revenue),Client)<20, '15M-20M',
IF(Aggr(Sum(Revenue),Client)>20 or isnull(Aggr(Sum(Revenue),Client)),'>20M',
))))),'<5M','5M-10M','10M-15M','15M-20M','>20M'),1,2,3,4,5)

But It did not work. Please help me on this.

Attached the sample application.

[kaanerisen]

Hi,

It would be better if you can make this binning in script side. But if you have to do that in front-end you can use dual function to combine a number and a string into a single record and you can use this number representation for sorting.

=IF(Aggr(Sum(Revenue),Client)<5,dual('<5M',1),
if(Aggr(Sum(Revenue),Client)>=5 and Aggr(Sum(Revenue),Client)<10, dual('5M-10M',2),
if(Aggr(Sum(Revenue),Client)>=10 and Aggr(Sum(Revenue),Client)<15, dual('10M-15M',3),
if(Aggr(Sum(Revenue),Client)>=15 and Aggr(Sum(Revenue),Client)<20, dual('15M-20M',4),
IF(Aggr(Sum(Revenue),Client)>20 or isnull(Aggr(Sum(Revenue),Client)),dual('>20M',5),
)))))

Hope it helps..