12 Replies Latest reply on Dec 19, 2016 1:21 AM by Simon Runc

Sort the result

Hi folks,

I hope all are OK.

I am new with Tableau and I have build the attach_1 work sheet which is describe the total profit and total lose by country my question is how can I sort it depends on the positive and negative signs. Moreover I found a way to select the positive and sort it DESC or ASC and do the negative as well in a same way see attach_2 but this doesn't working fine because I have use the date filter so if I change the date the positive and negative will be order in a differently way see the attach_3.

Is there a way to sort it such as the attach_1 and doesn't effect if I a change the date?

One more thing, is there a way to put the Dimeension between the negative and positive signs instead of the left poisition?

I appreciate your assistance in this matter.

Sultan

• 1. Re: Sort the result

hi Sultan,

So one way to achieve this is to use a Blue pill to "force" a sort in Tableau. By default Tableau sorts Blue pills in Ascending order (be that by value or alphabetically), which means the negative of the value is a descending sort!!

In the attached I've mocked up the YoY (for last year) in Super Store by Category...which is done via the [Sales Var YoY] measure

(ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))

I've then created the following formula off this (just the negative of the YoY formula)...

[Sales Var YoY - Sorter]

[Sales Var YoY]*-1

I then change this to be discrete (Blue) and bring it into the left of the thing I want to sort (SubCategory in this case). I then hide the header...as you'll see you can filter on the Months and it always re-sorts in descending order.

hope that helps and makes sense, but let me know if not

...not sure I understand what you mean in your second question...can you show me an example of what you'd like to see.

1 of 1 people found this helpful
• 2. Re: Sort the result

Thanks Simon I really appreciate it.

Ignore the second question I have a new one .

How can I sum the profit from the beginning of the seasons until specific date?

• 3. Re: Sort the result

Well it depends on how you want to show it...

do you want a running sum by month (bottom chart)? or do you just need a field whose SUM is the profit only to a specified date?

and on either option, how do you want to define the "Specific Date"? via a calculation, or a user filter/parameter?

If you let me know on the above I can let you know a way to achieve it.

• 4. Re: Sort the result

Thank you so much.

Sorry for asking so much questions. One more, How can I take the Top 5 + and Top 5 - in the same sheet.

• 5. Re: Sort the result

hi Sultan,

So I assume you mean top 5 and bottom 5?...

This thread gives several ways to do it, with good explanations (...personally I like the sets method)

• 6. Re: Sort the result

Hi Simon,

Thanks for getting back to me.

This is not what i want. I want to display the top 5 positive and the the 5 minus in the same time like the attachment.

Sultan

• 7. Re: Sort the result

So the same basic method works here...

First I create a RANK on YoY, which is the equivalent of the index() in Jim's post

[Rank on YoY]

RANK([Sales Var YoY])

I can then use his exact formula (substituting the index() for RANK)

[Show Top 5 and Bottom 5]

[Rank on YoY] <= 5

OR (SIZE() - [Rank on YoY]) < 5

and set up the compute using on the table calcs as follows (the red box indicates where you can set up the different nested calcs differently)   I've left the calculations in the view, so you can see what's going on...but you can just drag the [Show Top 5 and Bottom 5] field to the filter and set to true to only show the Top and bottom 5

• 8. Re: Sort the result

Simon you are the best thank you so much

• 9. Re: Sort the result

Hi Simon,

Last questio I pro you. How can I put the positive result and negative one in separate sheet? " the positive in sheet and negative in different sheet.

Sultan

• 10. Re: Sort the result

hi Sultan,

So to do this we can create the following calculation (on our YoY field)

[YoY Positive/Negative Filter]

[Sales Var YoY]>=0

we can add this to the filter shelf...and set to true (ensuring the compute using is Order Year)...this is the positives

then duplicate the sheet, and set the [YoY Positive/Negative Filter] to false (in the filter)...this is the negatives.

• 11. Re: Sort the result

Hi Simon,

This is not working fine on my sheet. Let me explain what I did.

1. I have two season data so I created a calculated field:   IIF(([UR_SEASON]) = 2016, [Sales],0) I called this 2016 Sales

2. Another cal filed another season IIF(([UR_SEASON]) = 2015, [Sales],0) I called this one 2015 Sales

3. Difference Calculated filed [2016 Sales]-[2015 Sales]      I called this field Diff

When I try to use your solution [Diff]>=0 it gave me something wrong for example Pakistan country it will appear in the positive and in the negative sheet.

Simon Runc

• 12. Re: Sort the result

I think this is due to the Diff being carried out at row level (so not an aggregated calculation).

When you create a field such as IIF(([UR_SEASON]) = 2016, [Sales],0)...when you bring it into the canvas it is brought in as a SUM, and so the Diff also needs to be wrapped in a SUM aggregation. Below is a table showing what happens at Row Level So if you make your Diff calculation SUM([2016 Sales])-SUM([2015 Sales]), they your >0 part will work over the aggregate, and should do what you want.