topic Re: How to rank a simple selection in QlikView

How to rank a simple selection

Sat, 23 Aug 2014 16:46:12 GMT

I'm sure this is a simple question for the experienced ones ... but as a bloody beginner to QV it's costing me an afternoon without success.

From the data in my (simplified) table

I want to plot a chart for each <customer> (i.e. customer in listbox) and the dimension <month>.

The data in the chart should be sum(amount) and the rank of the (selected) customer (by sum(amount))

However I put the expression, I either get the rankings of all months or no result.

I'm sure I have to work with AGGR but wherever I place it seems to be wrong.

Can anybody help please.

Re: How to rank a simple selection

nagaiank — Sat, 23 Aug 2014 17:28:57 GMT

Something like the attached application?

Re: How to rank a simple selection

Sat, 23 Aug 2014 17:46:32 GMT

Dear NagaianK,

thank you for your quick reply.

The application you attached contains the chart for sum(amount) for each (selectable) customer over the dimension month. In this chart I want to add a line that represents the rank of the selected customer by sum(amount) among all customers per month.

In my understanding that means I have to calculate sum(amount) for each customer and month and plot the rank of the result for my selected customer for each month. This is where I have my problem.

Your help would be much appreciated.

Re: How to rank a simple selection

rustyfishbones — Sat, 23 Aug 2014 17:46:51 GMT

Can you share a sample App or Data.

Re: How to rank a simple selection

rustyfishbones — Sat, 23 Aug 2014 18:00:13 GMT

Have you tried using the following expression

RANK( SUM ( Amount)

with Month as the Dimension and selecting Customer from a list box

Re: Re: How to rank a simple selection

Sun, 24 Aug 2014 10:29:41 GMT

Yes, I've tried RANK(Sum(Amount). But then it ranks the months, pls see attached app:

On sheet 'dimension month' I have the chart by month with possible selection of customer plottin the amount. In this chart I want to integrate the information how the seleced customer ranks among his peers for each month. I have plotted that on the sheet 'dimension customer' where it shows that customer A is 3rd in month 1, 4th in months 2 and 3 and ranks 1st in month 4. This is what I want to plot additionally in the chart on sheet 'dimension month' for a selected customer ... I just don't know how.

I'm sure I need a set analysis together with AGGR but however I put it I just don't get the right result.

Re: Re: Re: How to rank a simple selection

rustyfishbones — Sun, 24 Aug 2014 14:04:06 GMT

try adding an Expression like

AGGR(RANK(SUM(amount)),customer,month)

see attached, is that what you need

Re: How to rank a simple selection

Sun, 24 Aug 2014 15:05:52 GMT

Thank you for the quick reply, the app and your advice

.... unfortunatley the rankings that the expression returns are not correct.

I did a comparison of the results, expected vs expepression:

month	customer	Sum(amount)	Rank expected	AGGR(RANK(SUM(amount), customer, month)
1	B	200	1	2
1	C	150	2	3
1	A	100	3	4
1	D	85	4	4
2	B	180	1	3
2	C	170	2	1-2
2	D	160	3	2
2	A	110	4	3
3	B	220	1	1
3	C	170	2	1-2
3	D	145	3	3
3	A	120	4	2
4	A	210	1	1
4	D	195	2	1
4	C	100	3	4
4	B	65	4	4

As you can see the ranks from the expression are not representing the actual rank and I haven't managed to find out what they rank.

Re: How to rank a simple selection

rustyfishbones — Sun, 24 Aug 2014 16:37:33 GMT

Maybe because you have month as the first dimension

try

AGGR(RANK(SUM(amount)),month,customer)

Re: How to rank a simple selection

Sun, 24 Aug 2014 16:54:02 GMT

Sorry, didn't work. Now I have all customers ranked 1st for every month.

Re: How to rank a simple selection

Tue, 23 Sep 2014 14:05:08 GMT

I have managed to get the rankings in the chart below.

Now I would like to modify the chart that only the line for customer A is visible. I can't use the listbox object to select the customer as this would change the ranking result, i.e. I want the ranking to be calculated across all customers.

Which setting do I have to change to make only one line visible?