Rank Based On ALL Dimension Members

[#1]

Hello

I have the following rank expression.

=rank(sum(TotalCost),4)

However, is there a way to ignore a dimension selection?  So that the rank expression would return the rank based on ALL.

For example, I selected one Partner dimension member.  Even though only one Partner would return, I would like the rank expression to rank based on ALL Partner members, not based on the selected members.

Partner      Rank

"QlikView"     1

"Microsoft"     2

"Google"       3

Current Result (after filtering on "Google" Partner)

"Google"     1

Ideal Result (after filtering on "Google" Partner)

"Google"     3    

Sincerely.

[Jason_Michaelides]

Try:

Rank(Sum({<Partner=>} TotalCost),4)

Hope this helps,

Jason

[#3]

For some reason, now the StraightTable does not filter the Partner list, and shows all.

Previously, when I select "Google" Partner, only "Google" would return and rank shows 1.

I want to still return "Google" only, but rank would show 3.

With the new code <Partner=>, now the StraightTable does not filter Partner after I select "Google".  It shows all.

Help is appreciated!

[Jason_Michaelides]

This is because the set analysis bit is cancelling the Partner selection for the table. I think you need to use a data island but I can't think how right now. I'll have another look tomorrow.

[#5]

Add one more expression like sum(rank), sort by the expression value desending and hide the expression from presentation tab. Idea is to create a column whose value only exists for selected stuff.

Finally you can restrict the maximum rows visible to no:of partners selected.

Hope this works out,

Kiran.

[#6]

Can you elaborate? 

My current PartnerRank Expression: Rank(Sum(TotalCost),4)

You want me to add another expression: sum(rank)???

Why do I need to restrict maximum rows?  When I select Partner filter, It is returning one Partner (which is correct), just the rank value is not returning correct value based on ALL Partners (not just the one selected).

Basically, I just have 2 columns in my chart.

1 Dimension: Partner

1 Expression: PartnerRank

What I don't understand is the new column you want me to add and hide.  What does that do?  Does that help calculating the rank value?

Jason suggested PartnerRank to be: Rank(Sum({<Partner=>} TotalCost),4)

However, the ALL Partner set analysis is canceling out the chart filtering, so chart is not filtering at all.

Help is appreciated!

[Jason_Michaelides]

Because your rank value is calculated from Sum(TotalCost) it will vary with a Partner selection. Therefore you need to exclude the Partner selection from the Rank calculation which {<Partner=>} does.  However, in a chart with nothing more than a simple Partner dimension and the Rank expression, it's as if your Partner selection isn't happening.  A solution to your issue could be to change your chart dimension from "Partner" to:

If(Aggr(Sum(TotalCost))>0,Partner)

then you need to check the box on the dimension tab to suppress Null values.

That should work for you.  Basically, your expression is ignoring the Partner selection, but the dimension is calculated purely based upon partners that have sales. Obviously, only your selected partners will have sales so any partners selected will be in the list.  Of course, any partner without any sales will not be in the list, even if they are selected.

Would this do?

[#8]

Hi,

also a suggestion:

you want your selection on partner (and maybe also your other selections) to be ignored for your ranking.

in this case use set analysis =rank(sum({1} TotalCost),4)

see also p458 of the qv ref manual for the rank function

regards

[#9]

Hello Jason,

If(Aggr(Sum(TotalCost))>0,Partner)

I used your expression in the Partner dimension, but I am getting error "Allocated Memory Exceeded".

Is this related to syntax error?  Not sure what the error message meant.

Still struggling with the rank.

Here's my PartnerRank Expression.

Rank(Sum( {< Partner=, LOB={HOTEL} >} TotalCost),4)

Sincerely.

[swuehl · Accepted solution]

Hi captainlaw,

I don't think you need to change the dimension away from Partner.

Try instead using aggr() in the expression around the rank with clearing the field selection in the sum, see attached.

Regards,

Stefan