10 Replies Latest reply on Feb 18, 2016 11:07 AM by Imran Akbar

# Table calculation using rank

Hi,

I'm trying to translate some logic I have in SQL to Tableau.

My schema is like this (table header and two rows):

customer_id, date, A

1, 2015-01-01, 5

1, 2016-01-02, 6

I want to calculate the value of A, for the first date of each customer.

In SQL, I would do something like this:

SELECT

A,

RANK() OVER (PARTITION BY customer_id ORDER BY date) AS rank

....

QUALIFY rank=1

In Tableau, I see there's a rank function, and I can specify to partition over customer_id.

To look at just the first rank, would I just create a calculated field that says 'IF rank=1 THEN 1 ELSE 0 END' and filter on that being = to 1?

thanks!

• ###### 1. Re: Table calculation using rank

Imran,

In Tableau calculation, you can use LOD.  // Overview: Level of Detail Expressions

[First Date by Customer]

{fixed [Customer ID]:min([Date])}

[Value on First Date]

sum(if [Date]=[First Date by Customer] then [Value] end)

Thanks,

Shin

2 of 2 people found this helpful
• ###### 2. Re: Table calculation using rank

Great work Murakami. You Explained it very beautifully

• ###### 3. Re: Table calculation using rank

Rajeev,

Thank you for the kind comment.

"Beautiful" sounds very good.

Shin

• ###### 4. Re: Table calculation using rank

Hi Shin,

Thanks for that example.

I have a lot of rows of customers, so I don't want to actually display a value for each customer - I want an aggregate over all customers.

I need a separate value for each year (which is how I'm plotting them) per customer, so I changed the LOD expression to this:

{INCLUDE [Customer ID][Year] : MIN([Date])

but now the next calculated field isn't correct, as I can't match on [Customer ID]:

"sum(if [Date]=[First Date by Customer] then [Value] end)"

So I made a calculated field like this:

RANK([Date]) and selected two values to partition over: [Customer ID] and [Year]

so that I could then filter to ranks = 1

but then Tableau froze up and crashed

but I still don't know how to take the rank and get another measure for it.

thanks,

imran

• ###### 5. Re: Table calculation using rank

[year]

year([Date])

Change formula of original

[First Date by Customer]

{fixed [Customer ID],[Year]:min([Date])}

Thanks,

Shin

• ###### 6. Re: Table calculation using rank

Let's just simplify this by taking out the YEAR.

I cannot get a RANK over date partitioned by customer to work without crashing Tableau.

And I can't put a table calculation like RANK into an LOD expression like this:

{INCLUDE [Customer] : RANK(MIN([Date]))}

What other options do I have?

• ###### 7. Re: Table calculation using rank

I should clarify - I'm not looking to visualize this as a table, as I have over a million rows.  I am looking for a way to calculate column 'A' in the row with the earliest date for each customer as a calculation.  Ie I can't drag customer to the rows or columns shelf.

• ###### 8. Re: Table calculation using rank

The fundamental problem I'm having is that I need to filter at an individual row level, but plot at an aggregate level.

The LOD expression works fine, but I can't filter based on it.

- I can't filter on date match, as it's not aggregated (and if I pick SUM or AVG it sums over all rows, not by customer)

- I can't convert the calculated field like CASE WHEN Date Match = 1 THEN [A] ELSE NULL END to a dimension, so I can't use that instead

- I can't use rank, as it requires an aggregate field

• ###### 9. Re: Table calculation using rank

I'm afraid you are missing something?

You can use LOD calc for filter, or one of the main reason we use LOD is to make line items filterable..

ie)

if [Date] = [First days****] then "yes" else 'no" end

You don't ever need to use "Rank".

Shin

• ###### 10. Re: Table calculation using rank

The issue was that I had a date filter, and I wanted to get the first record of each customer in that date range.

So I used 'INCLUDE' in the LOD, instead of FIXED.

This caused it to become a measure, not a dimension.

After chatting with Matt in support, he realized I could add the date filter as a context filter, and keep the LOD as fixed.

This solved the problem.  Thanks Matt & Shin