Max Product

[#1]

I want to determine the best selling product for a region.

I have data like so:

Region	Prod1	Prod2	Prod3	Prod4	Prod5
1	29	23	23	29	26
1	20	24	39	25	27
1	21	25	33	22	20
1	20	34	23	26	24
1	24	30	34	31	24
2	28	31	30	23	28
2	25	32	25	25	30
2	22	34	23	33	21
2	25	30	39	35	30
3	26	31	22	32	20
3	30	27	24	25	30
3	29	23	28	20	21

I want a table like so:

Region	Best Seller
1	Prod 3
2	Prod 2
3	Prod 1

Region 1 gets prod 3 since it sold 152 units, region 2 gets prod 2 since it sold the most at 127 units.

Not sure what the expression needs to be.

Thanks

[swuehl]

Transform your crosstable into a straight table:

CROSSTABLE (Product, Value)

LOAD Region, Prod1, Prod2, Prod3, Prod4, Prod5

FROM YourSource;

Then create a straight table with dimension Region and as expression

=FirstSortedValue(DISTINCT aggr( Product, Region, Product) , -aggr(Sum(Value), Region, Product) )

[#3]

Is there a way to do this without altering the load?

[swuehl]

Is there a reason you want to keep that structure?

Having a straight table is advantegous for almost all kind of analysis.

Have a look at

The Crosstable Load