Rank Question

[#1]

I have the following data set:

Month     Product     Quantity

Jan               A          1000

Feb              A            500

Mar               A           100

Jan               B           500

Feb               B           900

Mar               B           200

Jan               C           200

Feb               C           300

Mar               C           700

The results I want is:

Month

Product

Quantity

Jan            A          1000

Feb           B             900

Mar           C             700

In other words I want to rank the top product quantity by month.  I tried the following:

1.  In the dimension - Month and =if(aggr(rank(Quantity),Month,Product)=1,Quantity)

2.  In the expression - sum(Quantity)

It does not seem to work.  Can someone advise.

David

[MK_QSL]

Dimension = Month

Expressions

FirstSortedValue(Product, -Quantity)

FirstSortedValue(Quantity, -Quantity)

[martinpohl]

see attached

regards

[MK_QSL]

Can you close this thread please?

[er_mohit]

try this in pivot table

Dim1 -Month

Dim2  =aggr(if(rank(Quantity)=1, Product),Month,Product)

check on suppress null value

expression

Max(Quantity)