CITY Dimension wise TOP 5 Members(Dimension) Total

Fri, 26 Jun 2015 10:29:15 GMT

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

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

MEMBER SELECTED : 'B'

OUTPUT:

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.

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

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

swuehl — Fri, 26 Jun 2015 10:33:01 GMT

Try

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

Fri, 26 Jun 2015 10:38:01 GMT

Hi,

Thanks a Lot.

jagan — Fri, 26 Jun 2015 10:46:24 GMT

Hi,

Try this expression

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

Regards,

Jagan.