topic Re: Helping with Ranking when returning 1 row in QlikView

Helping with Ranking when returning 1 row

Thu, 01 May 2014 10:43:07 GMT

I have an awards table in Qlikview thats ranks our branches based on certain KPI's Years to Sales sales etc

I have used the Rank() function and this works very well unless you filter on a branch then the Rank automatically becomes 1

I want to find a way of selecting a branch but keeping the original ranking of the whole 140 branches in the table.

I have tried using set analysis however when i select the branch as a filter it still shows all the branches

example=

rank(total-(Sum({$<BranchTable_MapBranchName=,BranchTable_CH_Flag={'Y'}>}[Order Net Qty]*CYPM)/Sum({$<BranchTable_MapBranchName=,BranchTable_CH_Flag={'Y'}>}[Order Net Cust]*CYPM))-1,3,1)

Some advise would be great

Re: Helping with Ranking when returning 1 row

MK_QSL — Thu, 01 May 2014 10:44:09 GMT

Try

SUM({1}

If possible, try to provide some sample data or apps !

Re: Helping with Ranking when returning 1 row

Thu, 01 May 2014 11:01:26 GMT

Thank Manish for your reply

I have changed the expression to if(BranchTable_CH_Flag='Y',rank(total -(Sum ([Order Net Cust]*CYPM))-1,3,1),0)

This returns the correct ranking when I select 1 branch however in the straight table it returns a rank of 0 for all the other branches not selected

What I’m trying to do is only display 1 row with say Rank 120