Sort order in Bar chart based on Count of tickets and it's percentage value, using two measures
Hi,
I have a data like this,
ORG
cnt
percent
AT
28
10.2%
CT
17
12.4%
BT
13
14.4%
DT
13
11.1%
ET
11
47.8%
DT
9
15.0%
GH
8
14.0%
FT
7
63.6%
IJ
7
63.6%
NTS
7
13.0%
DT
6
20.7%
CF
6
20.0%
HD
6
18.2%
SP
5
33.3%
TSP
5
19.2%
PRO
4
40.0%
VEP
4
26.7%
AF
4
20.0%
AG
4
14.3%
SD
4
10.8%
SW
4
8.2%
SE
3
60.0%
SQ
3
60.0%
SA
3
27.3%
SZ
2
27.3%
SX
2
23.1%
SC
2
16.7%
SV
1
15.8%
DS
1
15.0%
DR
1
13.0%
required output is Required out put is as shown below
ORG
cnt
percent
Sort required output
FT
7
63.6%
1
IJ
7
63.6%
2
ET
11
47.8%
3
PRO
4
40.0%
4
SP
5
33.3%
5
VEP
4
26.7%
6
DT
6
20.7%
7
CF
6
20.0%
8
AF
4
20.0%
9
TSP
5
19.2%
10
HD
6
18.2%
11
DT
9
15.0%
12
BT
13
14.4%
13
AG
4
14.3%
14
GH
8
14.0%
15
NTS
7
13.0%
16
CT
17
12.4%
17
DT
13
11.1%
18
SD
4
10.8%
19
AT
28
10.2%
20
SW
4
8.2%
21
SE
3
60.00%
1
SQ
3
60.00%
2
SA
3
27.27%
3
SZ
2
27.27%
4
SX
2
23.08%
5
SC
2
16.67%
6
SV
1
15.79%
7
DS
1
15.00%
8
DR
1
13.04%
9
In bar chart sort order, we tried using the below logic, if( Count of tickets >= 4,
percent, if( COunt of tickets <4,
Percent)
using this logic, till
SW
4
8.2%
21
it is working, but for below condition it's failing,
so based on Count of tickets , the sort should happen on Percentage (highest to smallest) two conditions 1) >=4 & <4 based on % it should sort, or if we can get a new column called Sort required output column it would be helpful
In data load editor try this: A: Load *, rowno() as Sort resident Main_table where cnt >= 4 order by percent desc; Concatenate(A)
Load *, rowno() as Sort resident Main_table where cnt < 4 order by percent desc;
If this doesnt work please convert your percent into numerical value by removing percent sign, and please also check whether cnt value is numerical or string.
