Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try
=sum({1} aggr( if(rank(Sum({1}TURNOVER))<=5, Sum({1}TURNOVER)), CITY, MEMBER))
Try
=sum({1} aggr( if(rank(Sum({1}TURNOVER))<=5, Sum({1}TURNOVER)), CITY, MEMBER))
Hi,
Thanks a Lot.
Hi,
Try this expression
=sum({1<CITY=P(CITY)>} aggr( if(rank(Sum({1}TURNOVER))<=5, Sum({1}TURNOVER)), CITY, MEMBER))
Regards,
Jagan.