Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
ankit777
Valued Contributor

Rank And Aggregate

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

1 Reply
MVP
MVP

Re: Rank And Aggregate

Try :

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

Capture.JPG