topic Re: Rank in Script in QlikView

Rank in Script

lockematthewp — Sat, 16 Nov 2024 20:19:41 GMT

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.

Re: Rank in Script

bharathadde — Fri, 02 Aug 2019 20:53:08 GMT

Try this

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

Re: Rank in Script

lockematthewp — Mon, 05 Aug 2019 12:30:34 GMT

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

Re: Rank in Script

bharathadde — Mon, 05 Aug 2019 15:31:45 GMT

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

Re: Rank in Script

lockematthewp — Mon, 05 Aug 2019 16:23:49 GMT

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

Re: Rank in Script

Frank_Hartmann — Mon, 05 Aug 2019 16:42:49 GMT

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

Re: Rank in Script

lockematthewp — Mon, 05 Aug 2019 17:19:40 GMT

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;