Announcements
cancel
Showing results for
Did you mean:
Creator 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)
• ### Script Rank

1 Solution

Accepted Solutions
Creator II
Author

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:

Resident ActiveOldestCritical;

6 Replies
Creator II

Try this

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

Creator II
Author

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

Creator II

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.#'))

Creator II
Author

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

Master II

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

Creator II
Author

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: