Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try changing your dimension to this
If(Aggr(Sum(Revenue),Client)<5, Dual('<5M', 0), If(Aggr(Sum(Revenue),Client)<10, Dual('5M-10M',1), If(Aggr(Sum(Revenue),Client)<15, Dual('10M-15M', 2), If(Aggr(Sum(Revenue),Client)<20, Dual('15M-20M', 3), If(Aggr(Sum(Revenue),Client)>20 or IsNull(Aggr(Sum(Revenue),Client)), Dual('>20M', 4) )))))
and use numerical sorting