CITY Dimension wise TOP 5 Members(Dimension) Total

[#1]

Dear All,

Below is the sample table and desire output what i am looking for. ( attached Excel and QVW for same)

Ex.

i have a sample table which have MEMBER, CITY AND TURNOVER.

SAMPLE TABLE

MEMBER	CITY	TURNOVER
A	DELHI	200
A	MUMBAI	100
B	DELHI	100
B	MUMBAI	300
B	PUNE	500
C	DELHI	50
C	MUMBAI	250
C	PUNE	600
D	DELHI	25
D	MUMBAI	50
D	PUNE	200
E	DELHI	225
E	MUMBAI	25
E	PUNE	125
F	DELHI	300
F	MUMBAI	110
F	PUNE	150

FIRST LEVEL OF OUTPUT: ( CITY wise TURNOVER of selected MEMBER)

MEMBER SELECTED : 'B'

OUTPUT:

CITY	Sum(TURNOVER)
	900
MUMBAI	300
DELHI	100
PUNE	500

The above is very simple and easy. now i required a extra COLUMN Name "TOP 5 MEMBERS TURNOVER Like below:

Here i have selected MEMBER 'B' so SUM(TURNOVER) getting CITY wise SUM of TURNOVER of MEMBER 'B'

Now in the new COLUMN i want sum of TOP 5 MEMBERs of that CITY in the NEW Column.

CITY	Sum(TURNOVER)	TOP 5 Members Turnover
	900
MUMBAI	300
DELHI	100
PUNE	500

for example if i selected MEMBER 'B' the final output be like Below:

CITY	Sum(TURNOVER)	TOP 5 Members Turnover
	900
MUMBAI	300	810
DELHI	100	875
PUNE	500	1575

810 in MUMBAI comes because top 5 Members TOTAL TURNOVER in MUMBAI City is 810. same go as for others city

I am adding you the sample QVW and Excel file for ready reference.

[swuehl · Accepted solution]

Try

=sum({1} aggr( if(rank(Sum({1}TURNOVER))<=5, Sum({1}TURNOVER)), CITY, MEMBER))

[#3]

Hi,

Thanks a Lot.

[jagan]

Hi,

Try this expression

=sum({1<CITY=P(CITY)>} aggr( if(rank(Sum({1}TURNOVER))<=5, Sum({1}TURNOVER)), CITY, MEMBER))

Regards,

Jagan.