9 Replies Latest reply on Nov 28, 2012 7:18 AM by Max Mozgovoy

How to count a max value for each row, and return its position in a table?

Hi,

my data looks like a table of Customers IDs and a number of purchases in different product categories (Columns 1 to 70).

IDColumn1Column2
...
Column69
Column70MaxValueInARowMaxPosition
ID1017...41172 (or Column2)
ID232...1501569 (or Column 69)
........................
IDn254...132251 (or Column 1)

...

I need for every customer (from 1 to N) create 2 calculated fields:

- first (MaxValueInaRow): should return the maximum value for the purchases for each customer ID,

- second (MaxPosition): should return the position of the category (column ID) for this maximum value

Can you please give some hint on how to do that?

Any help will be greatly appreciated!

Thanks!

• 1. Re: How to count a max value for each row, and return its position in a table?

Ideally you will want to reshape your data so that it is long and narrow, not wide. Is this in Excel currently?

http://kb.tableausoftware.com/articles/knowledgebase/preparing-excel-files-analysis

• 2. Re: How to count a max value for each row, and return its position in a table?

Actually, my data are too big to fit in excel.
And they are long and narrow, its a big table of more than 1M customers (rows) and 75 columns.

• 3. Re: How to count a max value for each row, and return its position in a table?

Any ideas?  I can't believe that such a powerful BI tool like Tableau cant calculate the maximum value in a row.

Transposition of the base is not an option because it consists of hundreds of thousands users, and we need to aggregate on each of them.

• 4. Re: How to count a max value for each row, and return its position in a table?

You could try custom SQL - the second answer looks like it may work for you if you're on SQL of some sort: http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns

If you're coming from a text file I don't know if that will work.

You may be able to use RAWSQL as a calculated field as well, depends on your source: http://kb.tableausoftware.com/articles/knowledgebase/raw-sql

You can also use nested if statements, but I doubt that will work with the number of columns you have.

• 5. Re: How to count a max value for each row, and return its position in a table?

Hi Max,

You wrote, "I can't believe that such a powerful BI tool like Tableau can't calculate the maximum value in a row." A helpful frame of reference for me as I've had some of the same reactions is the following: Tableau fits the mold of a Clay Christensen-style "disruptive" entrant into the BI market, in the sense that it does some things way better than vendors, a number of things not as well as other vendors, and totally doesn't do some things other vendors do. As far as I'm concerned, Tableau is the best by far at interactive data visualization and ease/speed of use, and wins on that, and part of the cost is that in cases like yours we have to resort to various workarounds to get the calculations we want.

In this case, Tableau chose to focus on working better with tall vs. wide data, and that instead of building out every conceivable calculation themselves, they would rely on the underlying data source for as much of the calculations as possible.

Cheers,

Jonathan

• 6. Re: How to count a max value for each row, and return its position in a table?

Alex, thanks again! I have the CSV file as the source.

But still, even for SQL, thats OK if you have some 3-5 columns, but if you have 75 of them?? It'll be tricky.

• 7. Re: How to count a max value for each row, and return its position in a table?

As an addendum, in the responses I've seen about trying to do this sort of calculation, most point that if you're even trying to do this, there are likely issues in the database design - i.e. it is not appropriately normalized - in other words you are trying to do something that is out of the bounds of normal use.

• 8. Re: How to count a max value for each row, and return its position in a table?

You may want to look at PowerPivot - I don't know if that will let you look across a wide file with that many rows: http://www.microsoft.com/en-us/bi/powerpivot.aspx

You could also look at writing your own code in Python or similar to add a max column

• 9. Re: How to count a max value for each row, and return its position in a table?

Thanks, Alex!

I'll try to use PowerPivot, hope it'll help!

As for db normalization thats ok if you dealing with the relational structure. But I dealing just with the plain raw data on transaction frequencies.

Jonathan, I personally like Tableau a lot, just want to add some flexibility.