Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
avinashkk696
New Contributor III

Bar Chart Sorting Issue

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.

 

 

 

 

 

1 Reply
Highlighted
kaanerisen
Contributor III

Re: Bar Chart Sorting Issue

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),
)))))

Untitled.png

Hope it helps..