Sorting in WEBI

[former_member479167]

Hi

I have an infoobject which is in below format

A.1.00

A.1.10

A.1.20

A.1.100

A.10.00

A.10.10

A.10.20

A.10.100

A.11.00

A.11.10

A.11.20

A.11.100

When i don't do any type of sorting , it shows the output in the below format.

A.10.00

A.10.10

A.10.100

A.10.20

A.1.00

A.1.10

A.1.100

A.1.20

A.11.00

A.11.10

A.11.100

A.11.20

By this i can see the system is giving priority to 0 instead of '.'

I want output in the below format. Can anyone tell me how to achieve this??

A.1.00

A.1.10

A.1.20

A.1.100

A.10.00

A.10.10

A.10.20

A.10.100

A.11.00

A.11.10

A.11.20

A.11.100

[amitrathi239 · Accepted solution]

follow these steps.

Create Part1=ToNumber(Substr(Substr([Sort];3;Length([Sort]));1;Pos(Substr([Sort];3;Length([Sort]));".")-1))

Part2=ToNumber(Substr(Substr([Sort];3;Length([Sort]));Pos(Substr([Sort];3;Length([Sort]));".")+1;Length([Sort])))

First sort on part1 ascending and then part2 ascending.After that you can hide both.

[former_member479167 · Accepted solution]

Hi,

i am not getting the desired output in this case.

Part 2 = =ToNumber(Substr(Substr([Capital Budget - Key (Not Compounded)];6;Length([Capital Budget - Key (Not Compounded)]));Pos(Substr([Capital Budget - Key (Not Compounded)];3;Length([Capital Budget - Key (Not Compounded)]));".")+1;Length([Capital Budget - Key (Not Compounded)])))

Capital Budget Part 1 Part2

ME.10.30 10 30

ME.1.10 1 0

ME.1.110 1 10

Can you please suggest what need to be done?? Part 1 is coming correct.

[amitrathi239 · Accepted solution]

why you have used 6 in the formula.in my formula i have used 3.place 3 and try

=ToNumber(Substr(Substr([Capital Budget - Key (Not Compounded)];6;Length([Capital Budget - Key (Not Compounded)]));Pos(Substr([Capital Budget - Key (Not Compounded)];3;Length([Capital Budget - Key (Not Compounded)]));".")+1;Length([Capital Budget - Key (Not Compounded)])))

[former_member479167 · Accepted solution]

I have placed 3 then i am not getting the desired Output

Capital Budget

Part 1

Part2

ME.10.30

10

30 ( This is as per desired )

ME.1.10

1

0 ( What i want here is 10)

ME.1.110

1

10 ( What i want here is 110)

Above is the output if i am putting 6

[former_member479167 · Accepted solution]

Hi,

thank you. I have changed it to 4 and i got as desired.

part 1 =ToNumber(Substr(Substr([Capital Budget - Key (Not Compounded)];4;Length([Capital Budget - Key (Not Compounded)]));1;Pos(Substr([Capital Budget - Key (Not Compounded)];4;Length([Capital Budget - Key (Not Compounded)]));".")-1))

part 2 ==ToNumber(Substr(Substr([Capital Budget - Key (Not Compounded)];4;Length([Capital Budget - Key (Not Compounded)]));Pos(Substr([Capital Budget - Key (Not Compounded)];4;Length([Capital Budget - Key (Not Compounded)]));".")+1;Length([Capital Budget - Key (Not Compounded)])))

[Abhilasha]

Hi Mohit,

In webi you have 3 types of sort - ascending, descending and custom.
Custom sort is where you can select whatever order you want for the values.

If the number of values are not large, you can go with custom sort.

But apart from that i don't really know any other workaround. Let see if anyone else has any ideas on this.

-Abhilasha