Sorting Issue

building reports desktopintelligence
1

Hello All,

I need to sort a column (% productivity) in descending order.
Problem with this column is , column has few numbers, as well as empty cells and #DIV0. When i apply descending sort, Empty cell and #DIV0 gets the top most position followed by remaining number cells.

I need to push all empty as well as #DIV0 values at the bottom of the table.

Kindly suggest the way.
Custom sort is not working, option is disabled.
Please help. Thanks in advance :smiley: :smiley: :hb: :hb:

bryanket :bahrain: (BOB member since 2009-10-22)

2

For example, use Alerter and define #DIV0 as -1. Then sort by desc and rows with #DIV0 changes to -1 and goes underneath. The same apply for Null values, consider as -2 or -1.
Also, you may want to hide, tweaks like that. This is the solution that I can think of.
Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

3

I don’t think that will work. Alerts are really just formatting. I don’t think they will affect sorting at all.

Steve Krandel :us: (BOB member since 2002-06-25)

4

Hi,

Add a new column using formula : = If (IsNull()) Then -1 Else 0

and then apply sort on that new column
and also apply sort on Colm1.

After then check for required result.

aparna.naik (BOB member since 2010-06-23)

5

Thanks All.

Its working correctly.

I used IsNull and IsError which will give me -1 and others as 0. And then i sort this column(of -1 and 0) first and then the original . Then hide the cloumn of -1 and 0.

Once again thanks. I appreciate. :rotf: :smiley:

bryanket :bahrain: (BOB member since 2009-10-22)

6

Custom Sort would have been better option for this.

swap_l (BOB member since 2009-04-09)

7

Hi Swapnil,

Can you pls explain , how to apply custom sort?

aparna.naik (BOB member since 2010-06-23)

8

Right click on the border of your table

Select Format Sorts
Select Add (add the column you want to sort)
Select Custom
Select Values (define your sort)

REB01 :us: (BOB member since 2004-11-29)

9

@ Swapnil…

Thanks…

but,

I clearly mentioned that Custom sort is not working… Its disabled…

bryanket :bahrain: (BOB member since 2009-10-22)