If you for some reason want all the ranks to be negative:
IF NOT ISNULL(A) THEN
-IIF(A = LOOKUP(A, -1), -PREVIOUS_VALUE(1), INDEX(), 1)
Note the need to double-invert the "repeat previous value" case.
What does: "an advanced dimensional set ordered by [A]" mean?
Hmm, anything you set up using the "Advanced" sub-dialog of the Compute Using, which may not have any visible "direction" on-screen. Better wording suggestions are welcome.
OK, I see what you mean, I thought you must be referring to some whole new concept I hadn't come across yet.
I agree, it's hard to express this succinctly. How about something like:
"Direction that [A] is sorted in the view, or the sort order specified in the Compute Using "Advanced" dialog."
Very informative Post, but I have hit a snag. I am trying to do a ranking using a ratio of measures from 2 separate databases.
Example. I am trying to rank Markets based on Calls per User. The calls value is coming from a MySQL Database, the number of users is from an .xls. I created a calculated field for Calls per user, but that calculated field is not a choice to Sort by. Unfortunately the data is confidential so I cannot send the book, but am I missing something or is Sorting by Calculated field from Multiple DBs not allowed in 6.0?
Thanks for the example. I'm trying to figure out how to show the rank change over time. If a product is ranked #1 but drops down to #2 the next and back up to #1, I want to be able to show this visually. I'm attaching a copy of the superstore data to show what I am trying to do. I'm using the formula for the standard rank and everything has the same rank over time or when I change the way it calculates it, it's not showing me the correct rank. In the past, I have had to create a set and calculated field for each time period in order to create the rank, but I don't want to have to do that each time. Is there a way to set up a table calc to have a rank for each time period?
Book6.twbx 982.3 K
Jilleneh, I changed your table calc from:
Results are computed along Month, Year of Order Date, Product 2 - Sub-Category (sorted descending by Sum of Sales).
Results are computed along Product 2 - Sub-Category (sorted descending by Sum of Sales) for each Month, Year of Order Date. Relative LOOKUP values are fetched at the Product 2 - Sub-Category level.
And that seemed to do the trick.
To be clear, that's Compute using: Advanced - Product 2 then MonthYear (sorted by Sales desc), At the level: Product 2, Restarting every: MonthYear
I don't understand your partitioning instructions, attached is what I came up with. In the attached image, I have the following:
For Compute using, Advanced:
- Month, Year of Order Date
- Product 2 - Sub-category
Order along: Sales SUM Descending
Restarting every: Month, Year of Order Date
Because within each Month we want to sort the Product Category Descending by the Sum of Sales.
My description reads:
"Results are computed along Product 2 - Sub-Category (sorted descending by Sum of Sales) for each Month, Year of Order Date."
Also because there were NULL values for some product-Month combinations, I changed the formula so it would not rank null values as 1
Here is the modified function:
IF NOT ISNULL(SUM([Sales])) THEN IIF(SUM([Sales]) == LOOKUP(SUM([Sales]), -1), PREVIOUS_VALUE(1), INDEX(), 0) END
This way both Unknown and NULL values for SUM([Sales]) are returned with NULL
Also, when visualizing rank like this, it makes sense to reverse the axis for the Rank field, the 1st or one with best value is charted at the top.
I attached a workbook of this in action, with null marks formatted to hide and break lines, along with another sheet that shows another way to look at ranking over time.
Unfortunately, the field that you would want to use for the Order Along option would be something like:
Calls Per User
SUM([Calls])/SUM([Market Users (Market Calls Users.xls)].[Users])
I do not believe it is currently possible to use a formula based on either a data blending calculation or a custom table calculation as the field to define the Order Along option with the Advanced Calculate within dialog.
I would recommend you start a new thread in the "Wouldn't it be nice" section of this forum as a feature request with your example use case.
Thanks for the feedback Joe! I got it to work by holing down CTRL and selecting the "Calls per User" and "Market" , then hitting the sort button at the top. Problem is it just creates a manual sort,so every month I have to download the workbook from Tableau Server, Re-Sort, then re-publish.
I will Start a new thread in "Wouldn't it be nice" today about this.
James, Thanks for posting this. I have a quick question that relates to applying this function to my own dataset.
I've managed to do the dynamic quarterly rank similar to what joemako was doing. The challenge comes about when an entity may have a value in later quarters (and thus get picked up in the sum[sales] formula), but if the product was not sold in earlier quarters its rank would be returned as 1.
So, how can I get items with no entries in each relevant quarter to return null, whilst leaving the winner with a '1' ranking?
Thanks for your help
The other thing I'd like to be able to do is to create a dynamic group/set of the top 10 in each period (year, quarter, or month), and another group of those ranked 11-20, and those 21-30. These groups would be dynamic in the sense that a party may be in one set in one period, and another set in the next period. Set membership wouldn't matter, only that aggregate of that group.
This would allow us to see the contribution of the top 10 parties/products to overall sales, and the contribution of the next 10, and the next 10. To round it off, a final group/set would have all the other (non-member of top 1-30) parties.
Any idea how this can be done?
This is a situation like the one above where you need a modified calculation, something like:
IF NOT ISNULL(SUM([Installed Capacity])) THEN IIF(SUM([Installed Capacity]) == LOOKUP(SUM([Installed Capacity]), -1), PREVIOUS_VALUE(1), INDEX(), 1) END
with the partitioning set like the attached dialog image.
Then you can create and place a function like:
IF [Rank] > 10 THEN "> 10" ELSEIF [Rank] > 0 THEN "1 - 10" END
for grouping, it should inherit the partitioning already set. This will create a group of nulls as well, you can either hide or filter them out.
Both these things are done in the attached workbook.
I'm getting a bit worried. Is the above just a workaround because the datasource does not handle the rank() function? I really hope so because I have the rank column but am unable to put it in this format:
ITEMS 2009 2010 rank(among the industry)\
chairs 1K 2K 2
tables 2K 3K 1
My sql result set looks like this:
company year chairs chair-rank tables tables-rank
abc 2009 1K 5 2K 2
abc 2010 2K 2 3K 1
def 2009 3K 1 5K 1
def 2010 4K 1 3K 2
To make matters worse I will only be using one ranking (of the current year). Any ideas? Do I need to change my SQL to accomadate the above tabular format cuz right now i'm having helluva time trying to get it right.