Create a rank to remain static when selections are made

[#1]

Hi,

I am new to QlikView and have been asked to create a dashboard of top users. I have created an expression to pick out my top users which seems to work, but now they want to see these same top users by department. I thought this would be easy, but when I add a filter for department, QlikView cleverly re-calculates my rank for just that department. How can I make my rank static and still select by department? Can anyone help please?! Thank you, Kate

[jonathandienst]

Please post the expression you are using for the rank.

[#3]

Hi,

Here it is, I'm sure there's lots wrong with it in general as well and would welcome any general tips on improving it. They wanted the rank calculated as a sum of three scores (the score is calculated as a rank in itself). The reason for the massive if statement is because Qlikview didn't seem to want to add the scores together if one was missing...

Thanks for any help you can give me!

=Count({<RiOID = {"=

Aggr(Rank(

if(SUM([CountKey]) > 0 AND SUM([NetBedDay]) >0 AND SUM([AdmissionCount]) > 0,

RANK(if(Aggr(sum(NetBedDay),RiOID)>0,-SUM(NetBedDay)),1,1)/vDistinctBedCount +

RANK(if(Aggr(sum(CountKey),RiOID)>0,-SUM(CountKey)),1,1)/vDistinctContact +

RANK(if(Aggr(sum(AdmissionCount),RiOID)>0,-SUM(AdmissionCount)),1,1)/vDistinctAdmission,

//if all scores are complete

if(SUM([CountKey]) > 0 AND SUM([NetBedDay]) >0,

RANK(if(Aggr(sum(NetBedDay),RiOID)>0,-SUM(NetBedDay)),1,1)/vDistinctBedCount +

RANK(if(Aggr(sum(CountKey),RiOID)>0,-SUM(CountKey)),1,1)/vDistinctContact,

//if bed days and contact score is complete

if(SUM([AdmissionCount]) > 0 AND SUM([NetBedDay]) >0,

RANK(if(Aggr(sum(NetBedDay),RiOID)>0,-SUM(NetBedDay)),1,1)/vDistinctBedCount +

RANK(if(Aggr(sum(AdmissionCount),RiOID)>0,-SUM(AdmissionCount)),1,1)/vDistinctAdmission,

//if bed days and admissions score is complete

if(SUM([AdmissionCount]) > 0 AND SUM([CountKey]) >0,

RANK(if(Aggr(sum(CountKey),RiOID)>0,-SUM(CountKey)),1,1)/vDistinctContact +

RANK(if(Aggr(sum(AdmissionCount),RiOID)>0,-SUM(AdmissionCount)),1,1)/vDistinctAdmission,

//if admissions and contact score is complete

if(SUM([AdmissionCount]) > 0 AND SUM([CountKey]) =0 AND SUM([NetBedDay]) =0,

RANK(if(Aggr(sum(AdmissionCount),RiOID)>0,-SUM(AdmissionCount)),1,1)/vDistinctAdmission,

//if only admissions score is complete

if(SUM([NetBedDay]) >0 AND SUM([AdmissionCount]) =  0 AND SUM([CountKey]) =0,

RANK(if(Aggr(sum(NetBedDay),RiOID)>0,-SUM(NetBedDay)),1,1)/vDistinctBedCount,

//if only bed day score is complete

if(SUM([CountKey]) >0 AND SUM([AdmissionCount]) =  0 AND SUM([NetBedDay]) =0,

RANK(if(Aggr(sum(CountKey),RiOID)>0,-SUM(CountKey)),1,1)/vDistinctContact,

//if only contact score is complete

0))))))) //everything else give 0

,1,1),RiOID)<51"}>} distinct RiOID)

[#4]

try to use set analyses like:

sum({<[Department]=>}myField)

the Department part of this will ignore selection of your department

[#5]

Hi, Thank you for your suggestion. I couldn't get this to work in set analysis though, it kept giving me funny figures. In the end I created a list box with my expression for rank. If I select top 50 here and then select department, it seems to work!

Thank you though, Kate