Set Analysis & Rank()

[#1]

Very new to QlikView & have tried to find an answer in the forums, but may not have searched the right terms. I think my query is easy to solve, but can't figure out what I'm doing wrong.

I have a straight table displaying Territory, Targets, Sales, SvT (Sales v Target) and Rank

The rank is calculated as

  =Rank(sum(Sales) / Sum(Targets))  or =Rank(SvT)  which also works

This is great, as if I filter on a Product or Month the ranking dynamically changes (which is what I expect, and want to see)

When I filter on territories, if I reduce the data displayed from all territories to just 10, then those territories are dynamically ranked from 1 to 10. This is also as expected BUT, I would also like to have a "semi-static" ranking, where changes to Product / Month are reflected in the ranking, but reducing the number of territories displayed isn't.

I think set analysis is the way to go

=Rank(sum({1}Sales) / Sum({1}Targets)) 

was my first attempt. This holds the ranking for all territories, but doesn't reflect changes to Product / Month. This I expected, and I started to get excited (although was disappointed that it displayed all territories but showed only a Rank for the excluded territories...how to hide the excluded territories that have no data except a Ranking??)

I've tried so many different variations, this is the best & the one I most expected to work, but it doesn't:

=Rank(sum({$<Territory = >}Sales) / Sum({$<Territory = >}Targets))

This shows the same output as the last example, i.e. filter selections have no impact on the ranking (but the original and intentionally dynamically ranking column still changes to reflect the selections.

Any help most gratefully received & thank you in anticipation

[swuehl]

Your last expression should reflect selections e.g. in Month field, as far as I see, don't know why it doesn't seem to work in your case.

It's most of the time much easier to help if you can post some sample data together with your expected outcome, and if possible, a small sample qvw.

[#3]

David, just to clarify: your only problem remaining is that you can't hide the rows where there is no data apart from the ranking which uses the set analysis? Can you upload the file (you can use "Scrambling" from under Document Properties beforehand if it's sensitive)?

[#4]

2 replies already...thanks & I'm mighty impressed.

Have attached an example document, which I have cleaned and reduced as much as possible.

To be clear, I have 2 problems:

1. The "Static Ranking" doesn't change when Product / Month filters change, which it should

(Dynamic Ranking changes to reflect changes in Product / Month AND Territory...it is the change in territory filters I want to remove for the "Static Ranking")

2. When I reduce no of territories selected, all remain displayed (N.B. I haven't yet even tried to fix this, so unless you can tell me very easily, don't waste time, as I have a large bottle of red wine & like a challenge )

Thanks again

[#5 · Accepted solution]

1) This is interesting: if you remove the dollar-signs from the expression, it works! Just use this:

Rank(sum({<Territory = >}Sales) / Sum({<Territory = >}Targets))

Whilst I expect this solves your problem, it does bother me that I can't explain this behaviour as the dollar-sign should just simply reference the current selection and should not make a difference! I will need to experiment with this a bit more to be able to explain it...

2) Dinner is waiting for me now along with an upset wife, so I better go! I'll check back later to see if  this has been answered and try to figure out if not.

[swuehl]

1) That's because the chart is set to an alternate state:

Set Analysis & Alternate States – Quick Tip » The Qlik Board!

2) The static rank returns a value for every territory, because you are ignoring the territory selection.

I am not sure what you want to achieve, but you can try only calculating the value when another column shows a value:

=if(Target,Rank(sum({<Territory = >}Sales) / Sum({<Territory = >}Targets)))

[#7]

Thanks for the input. Very pleased to have a solution, and also pleased as a novice to know that I'm not losing my marbles when what I have just been trained in, doesn't quite work as I thought it would.

There is still an interesting problem for me to follow through with, in that if I limit the territories displayed by filtering on BM / IHM (which are higher levels in the structural hierarchy) then the static ranking doesn't work...but if I reduce the territories by selecting multiple territories, it does. Oh the joys of being new to something...

My wife is also upset, and I have to cook, so won't even get chance to try and figure out the above and my original second point until tomorrow.

Thanks again & have a great weekend

[#8]

Thanks for the input & another solution.

The alternate states was an attempt to solve this on another sheet which I deleted from this uploaded version...I had forgotten the alternate states experiment I had tried was even in there (bad form on my part)

the "If" was what i was going to try once my wife was fed and watered, thanks for the syntax....you've saved me hours of experimentation.

Enjoy the rest of your evening