How do you find max row number among line items of a single product ID?

tableaucommunity@tableau.com — Thu, 18 Sep 2014 14:16:00 GMT

First, to introduce my data set. For customers with a given ID, there are multiple line items representing unique transactions. These transactions are identified by Row Number, ordered by time of transaction. The first row number for each customer is 1.

My goal: for a given historical date, I want to filter the topmost transaction row that occurs before the given date for each customer.

Problem I am having: I do not know how to create a calculated field MaxRowNumber that processes each row number by customer ID and returns the maximum.

Example: Say I have this data set and I want to look at 6/10/2014. End result I want is an extraction of all bolded+underlined line items.

Customer ID	Row Number	Transaction Date
A	1	6/1/2014
A	2	6/5/2014
A	3	6/12/2014
B	1	6/10/2014
B	2	6/10/2014
C	1	5/25/2014
C	2	6/1/2014
C	3	6/1/2014
C	4	6/12/2014
D	1	6/12/2014

Intermediate Step: All transactions before 6/10/2014 (inclusive)

Customer ID	Row Number	Transaction Date
A	1	6/1/2014
A	2	6/5/2014
B	1	6/10/2014
B	2	6/10/2014
C	1	5/25/2014
C	2	6/1/2014
C	3	6/1/2014

Final Result: Extraction of line items where row number = MaxRowNumber (or similar process for transaction date) for a given Customer ID that occurs before 6/10/2014 (inclusive)

Customer ID	Row Number	Transaction Date
A	2	6/5/2014
B	2	6/10/2014
C	3	6/1/2014

I am able to get to the intermediate step using a series of calculated fields. I don't know how to calculate MaxRowNumber as described above.

If you could help me with this, I would be most appreciative.

Thanks,

Rohit

Content in Tableau Community Forums

How do you find max row number among line items of a single product ID?