Rank (Sum) to scripts

[AngieO2019]

Hi All,

I'm trying to convert this chart expression into script expression:

Rank(sum({<ID>} Score))

Any leads?

[Taoufiq_Zarra]

can you share a sample data and the expected output ?

[AngieO2019]

Hi, I can only provide this sample data.

The premise is that, depending on the percentage that the user will input on the input box, all IDs and Scores will be tagged as "Included".

the formula that I used on the chart in QlikView is:

If(

aggr(

(count({<ID>}Distinct Total ID) - Rank(sum({<ID>} Score))) / (count({<ID>}Distinct Total ID)-1)

,ID)
>= (1 - ($(Percentage)))

, Dual('Include',1))

I was able to translate the other functions in scripts, however, I'm having issues with the Rank(Sum) part...

[Brett_Bleess]

I may have inadvertently stumbled upon things I think, check the following Help link:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Aggre...

Regards,
Brett

[Kushal_Chawda · Accepted solution]

Can you tell me how do you want to Rank your score? If score is same then repeat the previous rank and for next rank increment the current row position?

Data:
LOAD
    ID,
    Score
FROM [lib://Data/SampleData.xlsx]
(ooxml, embedded labels, table is Sheet2);

Left Join(Data)
Load Count(DISTINCT ID) as TotalID
Resident Data;

Rank:
Load ID,
     sum(Score) as TotalScore
Resident Data
Group by ID;

Left Join (Data)
Load ID,
     TotalScore,
     if(RowNo()=1,1,if(TotalScore<>Peek(TotalScore),RowNo(),
     Peek(Rank)))as Rank
Resident Rank
Order by TotalScore desc;

Drop Table Rank;

Final:
Load *,
     (TotalID-Rank)/TotalID as Rank%
Resident Data;

Drop Table Data;

[AngieO2019]

Hi kush141087,

We were actually trying to replicate the PercentRank function of Excel. We thought that RangeFractile is the best bet to this, however, it seems like it only replicates the values in the Scores column.