I think you're on the right track with LOOKUP().
Rank Change 2 = LOOKUP([index], -1) - [index]
As with most table calcs, the trick is setting compute using.
Since [index] is a table calc, [Rank Change 2] is a nested table calc. When you add this to the view, double-check the compute using. When you open the Edit Table Calculation dialog, you'll see a pull-down menu at the top with both table calcs, [Index] and [Rank Change 2]. [Index] should be as you had it before > Advanced > Month of Date and City should be in the right-hand Compute Using box with a sort order of SUM(Sales) descending. After clicking OK, verify Restarting Every is set to Month of Date.
Then, in the same dialog box, select Rank Change 2 and verify it's set to compute using Order Date (same as Table Across) and Restarting Every is set to None.
Note that fields used in the table calcs---[index] and SUM([Sales])---do not need to be in the view for the table calcs to work.
P.S. You may also want to use a more nuanced Rank calculation that takes into account ties and nulls. Something like
IF FIRST() == 0 THEN 1
ELSEIF LOOKUP(SUM([Sales]), -1) == SUM([Sales]) THEN PREVIOUS_VALUE(1)
ELSEIF SUM([Sales]) > 0 THEN PREVIOUS_VALUE(1) + 1
One other minor comment. You had [index] in the level of detail shelf. This isn't necessary.
Thanks for your answer in such short time Jim, very appreciate. I will try it later.
Thanks Jim, it works very well.
So as my understanding, since the compute using is set to Table across, so the "-1" in the lookup formula means to lookup the first value to the left, right? Similarly, if the compute using is set to Table down, then the "-1" in the lookup formula means to lookup the first value upward, right?
And thanks a lot for your meticulous suggestion of the Rank.
1 of 1 people found this helpful
Yes, that's right, whatever dimension is selected in Compute Using is used for addressing. Everything else is used for partitioning.
The table across / down / down then across, ... can be a bit confusing and problematic if the view gets rearranged, and it's usually better to specify the dimension directly. Table across in your example is the same as Order Date.
When you have some spare time, Jonathan Drummey pulled together a good intermediate / advanced summary table calcs here: http://drawingwithnumbers.artisart.org/wiki/tableau/table-calculations/
Great! Thank you very much.
One further question Jim, is it possible to set the city sorted by the latest month's Index?
I tried set the sort on "City" using Sort-->Sort by --> Field: Sales, Aggregation: Sum
But it seems tableau will calculate the total sum of the sales of selecting period, that's not what I want.
My goal is, for example, select October, November of Order Data, and City will automatically sort by the index of November. Is that possible? Can tableau support sort by aggregation?
1 of 1 people found this helpful
I'd have to think if there's a more elegant way of doing this, but you can create a new calculated field called Sort Order, which is last SUM(Sales) along Order Date. Make this discrete and add it to the Rows shelf in front of City. Then you can hide the values by unchecking Show Header in the Sort Order pill.
By default the sort order is increasing, so I made the SUM(Sales) negative and converted nulls to 0 with ZN().
Sort Order = ZN(-LOOKUP(SUM([Sales]), LAST()))
After adding the value, compute using should be set to Order Date for LOOKUP / LAST to work properly.
Thanks a lot, that's works great!