3 Replies Latest reply: Jun 26, 2015 6:46 AM by jagan mohan rao appala

# CITY Dimension wise TOP 5 Members(Dimension) Total

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
AMUMBAI100
BDELHI100
BMUMBAI300
BPUNE500
CDELHI50
CMUMBAI250
CPUNE600
DDELHI25
DMUMBAI50
DPUNE200
EDELHI225
EMUMBAI25
EPUNE125
FDELHI300
FMUMBAI110
FPUNE150

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

MEMBER SELECTED : 'B'

OUTPUT:

CITY Sum(TURNOVER)
900
MUMBAI300
DELHI100
PUNE500

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
MUMBAI300
DELHI100
PUNE500

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

CITY Sum(TURNOVER) TOP 5 Members Turnover
900
MUMBAI300810
DELHI100875
PUNE5001575

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.

• ###### Re: CITY Dimension wise TOP 5 Members(Dimension) Total

Try

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

• ###### Re: CITY Dimension wise TOP 5 Members(Dimension) Total

Hi,

Thanks a Lot.

• ###### Re: CITY Dimension wise TOP 5 Members(Dimension) Total

Hi,

Try this expression

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

Regards,

Jagan.