TOP <NUMBER> TOTAL in Qlikview

[#1]

Dear All,

i have table which shows CITY wise TURNOVER of particular selected MEMBER like:

i have Select MEMBER 'A'.

CITY	SUM(TURNOVER)
DELHI	100
MUMBAI	200
PUNE	150

Now my requirement is to get SUM(TURNOVER) of TOP 5 MEMBERS of that CITY. like

i have Select MEMBER 'A'.

CITY	SUM(TURNOVER)	TOP 5 MEMBERS OF THAT CITY TURNOVER
DELHI	100	500
MUMBAI	200	300
PUNE	150	550

Kindly let me know how can this possibel

Thanks in Advance.

Regards,

JKV

[#2]

PFA, use dimension limit.

[balasundaram]

Write a set expression by using rank function include member.

[jagan]

Hi,

If you attach some sample data or some sample file then it would be easier to help you, otherwise no one is interested to prepare data and then give solution.

Check below link

Qlik Community Tip: Posting Successful Discussion Threads

[#5]

below is the formula to get the RANK on a city of particular selected Member.

min(aggr(rank(NUM(sum({<MEMBER_NAME=>} TRADE_VALUE))),CITY_CLNT,MEMBER_NAME))

But i want sum of TURNOVER OF top 5 MEMBERS

[#6]

Dear All Please find attached Excel file for sample table and result table with color indicator

[#7]

Hi Navdeep,

Thanks for help, but that is not what i need.

the Member is not the part of the dimension in a table its just a part of the selection.

find attached excel sheet that will help you to get what exactly i need 

Thanks

[jagan · Accepted solution]

Use this expression.

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