Rank And Aggregate

[ankit777]

Hi All

I have data with three columns. State, City, Sales.

I wish to show State, and sum so sales for top 2 city in that state.

eg. INPUT

State                    City                    Sales

Karnataka               Bangalore          50

Karnataka               Mangalore          40

Karnataka               Hubli                   30

Uttar Pradesh          Lucknow            45

Uttar Pradesh          Kanpur               35

Uttar Pradesh           Unnao               25

OUTPUT:

State                    Sales

Karnataka               90

Uttar Pradesh          80

What expression or dimension shall I use?

Thanks

[tresB]

Try :

Sum(If((Aggr(Rank(Sum(Sales)), State, City))<3, Sales))