Agrr/Rank: Returning dimension of an Agrr function for a specific rank.

[Anonymous]

Hi All,

So basicly i want to return the value of a dimension where the rank of turnover by the same dimension is equal to 1. In the below table i have tried to illustrate what im doing. i want the expr colored with red(because it doesnt work ) to return the same value as the green expr that is in a table with dimension product. The difference between the two is that the green is in a table with a dimension and the red calculates on an agrr vector with the same dimension but im unable to return the dimension value for this agrr function.

if(rank(TOTAL agrr(sum(turnover),product))=1,product)
rank(TOTAL agrr(sum(turnover),product))
agrr(sum(turnover),product)
product	sum(turnover)	rank(sum(turnover))	If(rank(sum(turnover))=1,product)
A	10	9
B	5	11
C	30	3
D	45	2
E	7	10
F	19	6
G	16	8
H	17	7
I	3	12
J	50	1	J
K	20	5
L	25	4

Why?

I have a graph with turnover in the vertical axes and months on the horizontal i have maeby 30 products in the data but i want a bar for each of the top 5 products stacked based on turnover YTD (calc on all selections except products/dates)

The idea is that by use of rank and aggr i can return the product name where rank is 1 and another expr for where rank is 2 etc.:

label.1	=$(functionrank1)
expr.1	=sum({<product={$(functionrank1)}>}turnover)
label.2	=$(functionrank2)
expr.2	=sum({<product={$(functionrank2)}>}turnover)

But since the dimension isnt products in the graph i cant return this value, i guess it basicly boils down to being able to return the dimension from the agrr

vector where rank of the expr is 1.

Well does anyone of you have any tips for this problem?

Thanks in advance.

Niels

[lironbaram]

hei

check this example

hope it helps you

if not may be you can post a graphic example of the desired result

b.s

if you use qv 11 you can easly achive this with dimensions limtis

[Anonymous]

Yeah that works, thanks for the help. If you have time i actually have a follow up question i realized after getting to this point, say i want to have the stacked turnover for top 5 products on the left vertical axis as bars and a line showing the monthly accumulated turnover of the 5 products together in one line as the right vertical axis. I think it might be impossible to ignore the split on the 5 products defined in the dimensions tab?

[lironbaram]

your problem wbill be that i didnt fnd a way in qlikview to build a stacked bar chart with line

i seems that if you add second expression to a combo chart then automaticly it changes the bars to group mode

[Anonymous]

Actually it is possible to have stacked bar charts with line aswell as long as you keep the dimension out of the dimensions page for example if you have dimension month, and u want to stack the bars according to fieldx that can take values 1 and 2. then you have to only have the time field in the dimensions page. and in the expression create two expression where expr 1 has set analysis fieldx={1} and expr 2 has set analysis fieldx={2}. then u can stack these two bars. When u then create a line it will either sum both 1 & 2 and show as 1 value or u can split the line in two with two expr the same way as you did with the bars. The problem u encounter when having fieldx in the dimensionspage is that the line will automaticly connect from fieldx1 to fieldx2 for every value of month dimension.

[Anonymous · Accepted solution]

Right so it was actually possible to do the graph i wanted by combining the function you gave me and the advise i just gave above on how to have lines and stacked bars in a combo chart. basicly i have 6 expr now (top 6) that has

Sum({$<

product

={'$(=MaxString(Aggr(if(rank(sum(turnover)))=1, product),product)))'} >}turnover}

for rank 1 product =2 for rank 2 product etc.

the lines i have are accumulations of sum of turnover on the 6 products for this year, last year and previous year.

if(year=year(Today),RangeSum(Above(Column(7)),Column(1)+Column(2)+Column(3)+Column(4)+Column(5)+Column(6)))

if(year=year(Today)-1,RangeSum(Above(Column(8)),Column(1)+Column(2)+Column(3)+Column(4)+Column(5)+Column(6)))

if(year=year(Today)-2,RangeSum(Above(Column(9)),Column(1)+Column(2)+Column(3)+Column(4)+Column(5)+Column(6)))

Thanks for the help Liron.