Maintaining a constant rank based on a calculated field/column

[tripod6999]

Hello,

I am new to QlikView.

I have a table which has three columns:

First column: Course title (this is a field and each course title is unique)

Second column: No. of Applications (for each course title) (this is a calculated column)

Third column: Course Ranking Based on No. of Applications (this is a calculated column based on the second column)

I would like to add a fourth column with an expression in QlikView which keeps the ranking the same when I choose any specific course title. Someone has suggested using the AGGR function, but I cannot seem to get this to work as it should. Remember I am a newbie so a very simple explanation would be most useful, so no mention of scripts, variables, etc, if possible - just a simple expression please! Thank you.

[marcus_sommer]

To ignore selections you need a set analysis, maybe something like this:

rank(count({< Course >} Applications))

which would ignore all selections within the field Course.

[tripod6999]

Hello Marcus,

That doesn't seem to work as it should as the rankings appear to be inconsistent e.g. with that expression I have two courses with zero applications having very different rankings. One good thing though is the rankings given by the expression don't change when I click on them. I will try to play around with the expression to see if I can make it work. Thanks again. 

[marcus_sommer]

It sounds that you may not (only) want ignoring selections else ignoring a certain dimensionality from the object. To get this you need to add a total, like:

rank(count({< Course >} TOTAL <Course> Applications))

which would ignore all object-dimensions unless the listed <Course> which is further considered.

[tripod6999]

Hello Marcus,

Thanks again for that, but it appears to have the same problem as above (I of course may be doing something wrong). Somebody suggested using the AGGR and RANK functions together.

[tripod6999]

Hello Marcus,

Could it be that it's not working because I also have another selection by which I only want the results for a certain group/directorate (we have 8 directorates and I only want the ranking to work for a specific directorate)?

So the number of applications and rankings given in the second and third column are only for say directorate A, whilst maybe the expression above does it for all 8 directorates? Is there something I could add in the expression that limits the rankings produced to only directorate A?

I hope that makes sense.

Thanks.

[marcus_sommer]

My suggestions were just example about the functionality and syntax to ignore selections and/or object-dimensions within an expression. Of course you will need to adjust and/or extend it to the wanted/needed dimensions for the aimed view.

I suggest you duplicate this rank-calculation a few times and adds there more and/or other dimensions within the set analysis as well as in the TOTAL statement (respectively without specifying any dimension to ignore all dimensions) and see which results are returning by various selections. It's not difficult to comprehend the way of working with it and then you adjust them a bit more until you have the expected results.

Beside this it might be that you also need to set a TOTAL for the rank() which is not the same as the TOTAL within the count(). Therefore you may play with this option, too.

The usage of aggr() could increase the complexity quite significantly and should be only used if the wanted results couldn't be reached with the normal measurements.  

[tripod6999]

Marcus,

Thank you for your suggestion. I will play around with the formula you have kindly put forward. I will let you know if I come up with a  solution!

Thanks again.

Francis