Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

lockematthewp
Contributor II

Rank in Script

I need to create an expression in the script to determine the average age of the top 5 oldest active critical cases. I have these fields: [Crit] = Yes/No,  [Age]=#, [Status] = Active, Solved, … [Case #]=...

This is how I did it in an expression but this must be done in the script so I am unable to use set analysis.

Num(Avg({<Crit = {Yes}, Status = {Active}, Age = {"=Rank(Only({<Crit = {Yes}, Status = {Active}>} Age), 4) <= 5"}>} Age), '#,##0.#')

I would really appreciate any help.

Labels (1)
1 Solution

Accepted Solutions
lockematthewp
Contributor II

Re: Rank in Script

I solved the issue. I loaded the first 5 active critical cases with age descending. I then used another table to get the average of these five values.

ActiveOldestCritical:

First 5 Load Age as AOCAge,

Status as AOCStatus,

Crit as AOCCrit

Resident Final

where Status = 'Active' and Crit = 'Yes'

order by Age desc;

 

CaseAverages:

Load Avg(AOCAge) as AOCAgeAve

Resident ActiveOldestCritical;

View solution in original post

6 Replies
bharathadde
Contributor II

Re: Rank in Script

Try this

Num(Avg({<Crit = {Yes}, Status = {Active}, Rank(Only({<Crit = {Yes}, Status = {Active}>} Age), 4) = {" <= 5"}>} Age), '#,##0.#')

lockematthewp
Contributor II

Re: Rank in Script

I need to do this in the script so I am not able to use set analysis like this.

Highlighted
bharathadde
Contributor II

Re: Rank in Script

I don't know if it works

but just give it a try 

=if(Crit = 'Yes' and Status = 'Active' and  if(Crit = 'Yes' and Status = 'Active', Rank( Age), 4))  <= 5,Num(Avg(Age), '#,##0.#'))

lockematthewp
Contributor II

Re: Rank in Script

Unfortunately you cannot use the Rank() function in the script.

Frank_Hartmann
Honored Contributor II

Re: Rank in Script

can you share the qvw or at least some kind of raw data to replicate the issue?

lockematthewp
Contributor II

Re: Rank in Script

I solved the issue. I loaded the first 5 active critical cases with age descending. I then used another table to get the average of these five values.

ActiveOldestCritical:

First 5 Load Age as AOCAge,

Status as AOCStatus,

Crit as AOCCrit

Resident Final

where Status = 'Active' and Crit = 'Yes'

order by Age desc;

 

CaseAverages:

Load Avg(AOCAge) as AOCAgeAve

Resident ActiveOldestCritical;

View solution in original post