10 Replies Latest reply on Sep 18, 2014 7:16 AM by Jonathan Drummey

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

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 IDRow NumberTransaction Date...

A

16/1/2014
A26/5/2014
A36/12/2014
B16/10/2014
B26/10/2014
C15/25/2014
C26/1/2014
C36/1/2014
C46/12/2014
D16/12/2014

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

Customer IDRow NumberTransaction Date...

A

16/1/2014
A26/5/2014
B16/10/2014
B26/10/2014
C15/25/2014
C26/1/2014
C36/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 IDRow NumberTransaction Date...
A26/5/2014
B26/10/2014
C36/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

• ###### 1. Re: How do you find max row number among line items of a single product ID?

A packaged workbook would have been nice.   See attached. Make sure you get the table calc partition/addressing set correctly.

Cheers,

--Shawn

1 of 1 people found this helpful
• ###### 2. Re: How do you find max row number among line items of a single product ID?

Thanks Shawn! Sorry about not creating a packaged workbook - will keep that in mind for next time.

How would I recreate the partitioning in this table calculation for Calcuation2 if I did not want to add the Customer ID, Row Number, and Transaction Date fields into the Rows column?

E.g. I wanted to apply this filter, but then create a visualization that sums customer transaction amount by store shopped at (e.g. if there was another column called Store Number).

Thanks,

Rohit

• ###### 3. Re: How do you find max row number among line items of a single product ID?

What do you want of the rows/columns shelves? Nothing?

• ###### 4. Re: How do you find max row number among line items of a single product ID?

I don't want Customer ID, Row Number, or Transaction ID in the row/columns shelves. But, I want to add other data fields on the row/columns shelves and recreate the same filter.

• ###### 5. Re: How do you find max row number among line items of a single product ID?

Ideal scenario would be that I can create a new calculated field that will store the results from the filters in the packaged workbook. I can than apply this new field as a filter in subsequent worksheets.

• ###### 6. Re: How do you find max row number among line items of a single product ID?

That's not how table calculations work in Tableau. Any dimension in the viz will need to be accounted for in the table calculation. Pull dimensions out of the viz and you'll break the table calculation. Everything in Tableau is inter-dependent. Change something from continuous to discrete and your viz will change. Remove a pill from a shelf and your viz will change. This is especially true of table calculations.

So there really isn't a way that I know of to do what you want.

Cheers,

--Shawn

• ###### 7. Re: How do you find max row number among line items of a single product ID?

Is there no syntax within Calculated Fields in Tableau to partition data and run a calculation?

• ###### 8. Re: How do you find max row number among line items of a single product ID?

In a table calculation field there is a default setting that allow you to preconfigure partitioning and addressing. But they won't hold unless the addressing fields are already in the viz. However this doesn't sound like what you're looking for.

So the answer really is no. Not that I know of.

--Shawn

• ###### 9. Re: How do you find max row number among line items of a single product ID?

Hi All,

Please share the logic since i'm using Tableau Public Desktop & Cant open your Workbooks until it is available in Tableau Public.

Thanks

• ###### 10. Re: How do you find max row number among line items of a single product ID?

Hi Rohit,

There are three levels of calculation in Tableau - record level, aggregate, and table calc. When you want to look across rows in the the data, that means using an aggregate or table calc. Although Tableau will let us compare aggregate & table calcs, it won't let us compare record-level calcs with aggregates or table calcs. Since that MAX(row number) has to be an aggregate, we currently*** can't write a calc like IF (this row number) == MAX(row number) then "latest row"  END. Therefore one solution is to do what Shawn did, namely bring the necessary dimensions into the view and then use table calcs. It's possible to nest table calcs, so depending on what you want you may be able to get the desired results inside Tableau.

Alternative solutions include using a data blend (which still requires working with aggregates), or a custom query/custom SQL. In my work where I'm often needing to get the latest result, the Tableau data source has a main query and a subquery that gets the max value. I did some examples of this at Find Earliest/First/Least and/or Latest/Last/Maximum of a Field and Use that for Other Fields.

*** There are a couple of related feature requests to enable this kind of thing to be done at http://community.tableau.com/ideas/3601 and http://community.tableau.com/ideas/1316

Jonathan