topic Re: Max Product in QlikView

Max Product

Tue, 09 Feb 2016 20:32:42 GMT

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

I have data like so:

I want a table like so:

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 — Tue, 09 Feb 2016 21:33:45 GMT

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) )

Tue, 09 Feb 2016 22:08:01 GMT

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

swuehl — Tue, 09 Feb 2016 22:20:55 GMT

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