Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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) )
Is there a way to do this without altering the load?
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