Custom Sort in webi

[Former Member]

Hi,

I have created a variable which gives ranges of Amount.

$0-$25K

$25k-$50k

$50k-$100k

$100k-$250k

$250k-$500k

>$500K

so that we can check number of invoices coming in the range.

Since Range is a dimension values are getting sorted by itself and when I am doing custom sort system is not accepting custom sort.

It shows in below way:

$0-$25K

$100k-$250k

$250k-$500k

$25k-$50k

>$500K

$50k-$100k

Please guide to get correct sort order.

Thanks,

Ankit

[Former Member]

Hi Ankit,

As you are using XI 4.1, you can use hide column feature.

Create a variable as follows:

[sort] = If ( [range] = "$0-$25K") Then 1

               Else If ([range] = "$25k-$50k") Then 2

                  Else If ([range] = "$50k-$100k" )Then 3

                    Else If ([range] = "$100k-$250k") Then 4

                       Else If ([range] = "$250k-$500k" ) Then 5

                Else 6

Put this [sort] column in your column, sort it in ascending order and hide it.

Hope it will help.

Regards,

Yuvraj

[Former Member]

Hi Yuvraj,

It has been long time to this issue since we got busy in some other work but now we have to provide solution of the issue.

I have used your suggestion and created Sort variable where I have given 1,2,3,4,5,6 for different range of values and made it hidden in support of other column beside it. but when I am filtering the tab using filter bar YEAR Object then this sort is getting failed.

Initially report shows

Hide     ValRange     NumberofSupplier

1           0-25               9

2           25-50             19

3          50-100            7

4         100-250          10

5          250-400          11

6          >400               2

After selecting Year filter = 2015 in Filter Bar

It becomes

Hide     ValRange     NumberofSupplier

5           250-400          5

1           0-25               7

2          25-50              16

3          50-100             6

4          100-250           3

6          >400               2

Please guide in resolving the issue.

Thanks and Regards,

Ankit

[sateesh_kumar1]

Hi Ankit ,

I think when you generated the report and applied custom sort for first time , the 5th value may not be existed .When this value comes into picture custom sort automatically resets and push the new value at some order .

Make sure your variable (generates 1,2,..)  is a measure . Then just apply ascending order .

[Former Member]

Hi Sateesh,

If I make variable that generates 1, 2, 3, 4, 5, 6 as measure then how we can make it hide from table view.

Since,

we only have to show

ValRange      NumberofSupplier

We are using Hide_variable(1,2,3....) so that we can get custom sort.

May be I haven't understood your point.

[mhmohammed]

Hi Ankit,

Don't make that variable as a Measure, make it a Dimension. After that, add it to the table, sort on it (remember to remove sort on all other columns, else, it wouldn't work) and hide it by right clicking on that column -> Hide -> Hide dimension option.

Hope that helps.

Thanks,

Mahboob Mohammed

[sateesh_kumar1]

HI Ankit ,

Sorry , completely forgotten to hide. Make a detail variable with data type as number and apply sort and hide .

[Former Member]

Hi Sateesh,

Can you further elaborate on how we can create detail variable of data type number in webi.

As I told in earlier thread

I am able to do below:

Hide     ValRange     NumberofSupplier

1           0-25               9

2           25-50             19

3          50-100            7

4         100-250          10

5          250-400          11

6          >400               2

But after selecting Year filter = 2015 on Filter Bar

It becomes

Hide     ValRange     NumberofSupplier

5           250-400          5

1           0-25               7

2          25-50              16

3          50-100             6

4          100-250           3

6          >400               2

I am not getting any idea why webi is behaving in such way.

I am moving towards the conclusion that webi confuses itself when we use multiple If....Elseif....Elseif....Elseif...Else but I need your vote of confidence to agree on my conclusion.

Otherwise, if we do have any solution then please guide me to resolve it.

Warm Regards,

Ankit

[sateesh_kumar1]

Hi Ankit ,

Hope you read the NOTE attached by Amit . It's by behavior, if you are using Calculation context operators in the variable derivation .

Is the report on BW. If not try creating the logic in universe .

[amitrathi239]

Hi,

Share the manage sort screenshot.

See the attached screenshot.If you will manually rearrange the order then order will come correct in the table.

Amit

[Former Member]

I am doing custom sort from Advance menu only but it isn't working:

Thanks,

Ankit

[amitrathi239]

Hi,

What is your BO version?

Amit

[Former Member]

Its 4.1 SP5 Patch4

[amitrathi239]

Hi,

Check this SAP Note.Similar issue is mentioned.

2137733 - Custom sort based on a variable containing "If Then Else", is not correctly applied on Web...

Amit

[Former Member]

Ok.

In same way I have created the variable so I think it is the reason I cannot do custom sort.

I have given A, B, C, D, E in

A$0-$25K

B$100k-$250k

C$250k-$500k

D$25k-$50k

E>$500K

F$50k-$100k

it helped me in sorting. Can we hide first character and keep applying the sort.

If yes can you tell how we can trim first character in variable.

Thanks,

Ankit

[amitrathi239]

Hi,

in this case create two variable.One is without adding A,B,C etc.Second one is normal variable which you wants to display in the report.

Drag both the variables in the report.Add custom sorting on the variables starts with A.Later right click and hide the Dimension.

Amit

[Former Member]

Did same thing but I have added linking from this table to another table so when I click on any cell for navigation then in input control it shows both variables as in:

Var_Spend1 = B$25K-50K

Var_Spend2 = $25K-50K

That can be annoying for users.

So I am looking for any other work around.

Thanks,

Ankit