Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.
This worked as well as the solution below. Thank you Kush, I knew i was close just not there yet.
QVELHoff glad that you were able to figured our