topic Rank (Sum) to scripts in QlikView

Rank (Sum) to scripts

AngieO2019 — Sat, 16 Nov 2024 00:22:07 GMT

Hi All,

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

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

Any leads?

Re: Rank (Sum) to scripts

Taoufiq_Zarra — Tue, 07 Jul 2020 10:34:48 GMT

can you share a sample data and the expected output ?

Re: Rank (Sum) to scripts

AngieO2019 — Tue, 07 Jul 2020 11:24:01 GMT

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...

Re: Rank (Sum) to scripts

Brett_Bleess — Tue, 07 Jul 2020 18:04:58 GMT

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/AggregationFunctions/max.htm

Regards,
Brett

Re: Rank (Sum) to scripts

Kushal_Chawda — Tue, 07 Jul 2020 21:51:12 GMT

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;

Re: Rank (Sum) to scripts

AngieO2019 — Wed, 08 Jul 2020 06:46:04 GMT

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.