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.
Use Blue Pill to Sort.twbx 449.9 KB
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?
cool...glad it did the trick.
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.
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.
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)
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
Use Blue Pill to Sort.twbx 452.2 KB
Simon you are the best thank you so much
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.
Thanks in advance,
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.
Use Blue Pill to Sort.twbx 480.4 KB
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.
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.