Rank in set analysis with rank calculation option

[NickHoff]

I created an inline table to give me an option which calculation i'd like to sort my rank by:

LOAD * INLINE [

Metric, Expression

Volume, COUNT(DISTINCT SurgicalCaseSk)

Meditech Cost, (SUM(ItemCostAmt)/COUNT(DISTINCT SurgicalCaseSk))*SUM(SurgicalUsedQty)

Meditech Cost Per Case,SUM(ItemCostAmt)/COUNT(DISTINCT SurgicalCaseSk)

Supply Chain Cost Per Case,(SUM(ContractLIUOMPriceAmt)/COUNT(DISTINCT SurgicalCaseSk))

Supply Chain Cost,(SUM(ContractLIUOMPriceAmt)/COUNT(DISTINCT SurgicalCaseSk))*SUM(SurgicalUsedQty)

];

I have the following set which is not working as intended:

=COUNT({<ProcedureSurgeonName = {"=RANK(Expression),4)<=$(vTopPhysicians)"}>}DISTINCT SurgicalCaseSk)

The set will work if I use an individual calculation and a nested if statement instead of Expression.  However, I am told I can include the field name for the parameter with the expression.  I've tried wrapping the expression in the inline table " ", but that didn't work either

[Kushal_Chawda · Accepted solution]

Hi,

Use the below expression

=COUNT({<ProcedureSurgeonName = {"=rank($(=Expression),4)<=$(vTopPhysicians)"}>}DISTINCT SurgicalCaseSk)

You need to use $ to evaluate the calculation of expression

Please see the attached app I tested.

[rubenmarin]

Hi Nick, -1 means True, 0 means False.

If you want to see only the rank the expression should be:

=RANK(Expression,4)

Anyway, be aware that set analisys is calculated before the rows, so no dimension will affect that calculation inside the set analisys, and using a rank of an expression without dimensions most probably will return '1'.

And if '1' is less or equal than vTopPhysicians the expression "=RANK(Expression,4)<=$(vTopPhysicians)" will return '-1'.

In set analisys I think is returning true for all values so the filter has no effect.

[NickHoff]

I figured it out last night.  Qlikview won't read the Expression as an inline table.  I had to create a variable $(vRank) = Expression.  So when it evaluated the variable it took it in as the expression that was built in the inline table. 

[NickHoff]

This worked as well as the solution below.  Thank you Kush, I knew i was close just not there yet.

[Kushal_Chawda]

QVELHoff‌ glad that you were able to figured our