topic Re: Rank() from two dimension Aggr() in QlikView

Rank() from two dimension Aggr()

Thu, 25 Jun 2015 12:09:35 GMT

Dear All,

Please let me know how can get the Rank() from two Dimension Table ( via Aggr() function)

i want to get the Member rank of a specific city like 'MUMBAI'

RANK(aggr(Sum(TRADE_VALUE),CITY,MEMBERID))

Kindly help

Re: Rank() from two dimension Aggr()

sunny_talwar — Thu, 25 Jun 2015 12:18:08 GMT

Do you have a sample that you can share?

Re: Rank() from two dimension Aggr()

swuehl — Thu, 25 Jun 2015 12:19:28 GMT

In which context are you using this expression?

In a chart using which dimensions?

In a text box?

Rank() should be used within a table, not as an aggregation outside.

= Max( aggr( Rank(Sum(TRADE_VALUE), CITY, MEMBERID))

Max() is just a guess, no idea what you are trying to achieve.

Re: Rank() from two dimension Aggr()

Thu, 25 Jun 2015 12:42:05 GMT

i am using in CITY dimensions

Re: Rank() from two dimension Aggr()

Thu, 25 Jun 2015 12:44:32 GMT

there is my requirement.

i want to select a Member and want to show CITYWISE, TRADE_VALUE of a Member. But along with the TRADE_VALUE, I also want to know is Rank() CITYWISE

Re: Rank() from two dimension Aggr()

swuehl — Thu, 25 Jun 2015 12:45:45 GMT

Ok, within a chart with dimension CITY.

And you select a Member, but want to return the rank compared to all (unselected) members?

Try

= Min( aggr( Rank(Sum({1} TRADE_VALUE), CITY, MEMBERID))

to ignore the Member selection for the rank calculation.

Re: Rank() from two dimension Aggr()

Thu, 25 Jun 2015 12:45:49 GMT

becasue each city there are multiple member so according to members turnover on city what is his rank()

Re: Rank() from two dimension Aggr()

Thu, 25 Jun 2015 12:48:03 GMT

below is my requirement with sample data

MEMBER	CITY	TOTAL
A	MUMBAI	100
A	PUNE	50
A	DELHI	200
B	DELHI	100
B	PUNE	250
B	MUMBAI	200
B	KOLKATA	100
C	MUMBAI	50
C	DELHI	500

Now when i select Member 'A' i want to get CITY wise TOTAL along with the rank compare to other MEMBERS like

CITY	A-TOTAL	RANK
MUMBAI	100	2
PUNE	50	2
DELHI	200	2
KOLKATA	0	0

Kindly note this is just for a sample purpose. the actual records have more dimensions (YEAR,MONTH,DATE,etc)

Re: Rank() from two dimension Aggr()

swuehl — Thu, 25 Jun 2015 13:04:33 GMT

= Min( aggr( Rank(Sum({1} [TOTAL])), CITY, MEMBER))

seems to work for me.

Re: Rank() from two dimension Aggr()

Thu, 25 Jun 2015 13:14:37 GMT

Hi,

i think you did not get my requirement, below is sample

MEMBER	CITY	TOTAL
A	MUMBAI	100
A	PUNE	50
A	DELHI	200
B	DELHI	100
B	PUNE	250
B	MUMBAI	200
B	KOLKATA	100
C	MUMBAI	50
C	DELHI	500

In the above case if we see closely then we found in Mumbai, 'A' MEMBER have '2' Rank , and in PUNE, 'A' have '2' RANK.

So when i want to show CITYWISE TOTAL of 'A' Member, on the same time i want to show his TOTAL RANK with compare to other Member. like 'B' TOTAL is more and 'C' TOTAL is less so at MUMBAI he RANK '2' as on

CITY	A-TOTAL	RANK
MUMBAI	100	2
PUNE	50	2
DELHI	200	2
KOLKATA	0	0

here RANK() should me caluclated on the basis on MEMBERS respective CITY .

Re: Rank() from two dimension Aggr()

Thu, 25 Jun 2015 13:16:47 GMT

All City Returning 1

Re: Rank() from two dimension Aggr()

swuehl — Thu, 25 Jun 2015 13:20:26 GMT

Well, I have loaded your above table, created a straight table with Dimension CITY and as expression

= Min( aggr( Rank(Sum({1} "TOTAL")), CITY, MEMBER))

then selected Member A:

CITY	= Min( aggr( Rank(Sum({1} "TOTAL")), CITY, MEMBER))	sum("TOTAL")
	2	350
DELHI	2	200
MUMBAI	2	100
PUNE	2	50
KOLKATA		0

Select B:

CITY	= Min( aggr( Rank(Sum({1} "TOTAL")), CITY, MEMBER))	sum("TOTAL")
	1	650
KOLKATA	1	100
MUMBAI	1	200
PUNE	1	250
DELHI	3	100

Select C:

CITY	= Min( aggr( Rank(Sum({1} "TOTAL")), CITY, MEMBER))	sum("TOTAL")
	1	550
DELHI	1	500
MUMBAI	3	50
KOLKATA		0
PUNE		0

Looks to me like what you requested. Have you even tried?

Re: Rank() from two dimension Aggr()

Thu, 25 Jun 2015 13:47:25 GMT

Thanks a lot .

its working fine as per requirement,