Rank in Script

[lockematthewp]

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.

[bharathadde]

Try this

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

[lockematthewp]

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

[bharathadde]

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]

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

[Frank_Hartmann]

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

[lockematthewp · Accepted solution]

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;