8 Replies Latest reply on Apr 5, 2017 12:40 PM by Vishal D

# Sort based on custom calculation

Hi I want to show below chart from worst to best (-ve[lowest] to +ve[highest]) but first show the bars which are outside of tolerance and then followed by the bars which are inside the tolerance.

Tolerance Band = +0.5% to -0.5%

Attached twbx in 10.0.0

• ###### 1. Re: Sort based on custom calculation

Hi Vishal,

Here's one solution:

Create a calculated field that determines if the bars are outside the tolerance. (I used abs(SUM([Cumulative Gain/Loss (%)]))<=.5).

Then, drag this field to the columns shelf. This should group all of the ones outside the band first. You can then:

2) remove the column line that divides the graph by moving the "level" to the lowest point.

The final result is this:

Does that help?

Michael

1 of 1 people found this helpful
• ###### 2. Re: Sort based on custom calculation

This is perfect, I more twist.

Is it possible to plot by the abs(%) highest to lowest withing tolerance followed by out of tolerance.

• ###### 3. Re: Sort based on custom calculation

I believe so! You'll want to create another calculated field for abs(%). Formula can just be abs([Cumulative Gain/Loss (%)]).

Then, you can right click the "Pipeline Facility" pill in the columns shelf and select "Sort". Then, you can sort Descending by the abs(%) field.

Hope that helps!

Michael

• ###### 4. Re: Sort based on custom calculation

Works like a charm !!

• ###### 5. Re: Sort based on custom calculation

Looks like I celebrated soon.

Attached twbx

Its works for one division 'AM' but when I try others the sorting doesn't seem to be working.

For 'BI' not working

• ###### 7. Re: Sort based on custom calculation

Hey Vishal,

Took me a while, but I figured out the problem. The formula as is was taking the Absolute Value of the gain/loss. Then, when you sorted based on it, you were sorting based on the *SUM* of the *Absolute Value*.

What you actually want is to sort based on the *Absolute Value* of the *SUM*.

If you change the formula for absolute value to be: ABS(sum([Cumulative Gain/Loss (%)])) is should work. (You'll have to redo the sort on Pipeline Facility.) Apologies for not catching this earlier!

Let me know if that works

Michael

1 of 1 people found this helpful
• ###### 8. Re: Sort based on custom calculation

Works perfect !!