topic Re: Any Similar function in qlikview as percent_rank in SQL ? in QlikView

Any Similar function in qlikview as percent_rank in SQL ?

aniketsr — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Any Similar function in qlikview as percent_rank in SQL.

Re: Any Similar function in qlikview as percent_rank in SQL ?

HirisH_V7 — Mon, 22 Apr 2019 10:31:36 GMT

Try this:

Temp:
LOAD * INLINE [
Customer, Sales
A, 170
B, 110
C, 120
];

Sales_sum:

LOAD sum(Sales) as Sum_Sales

RESIDENT Temp;

LET vSales_Sum = PEEK('Sum_Sales',0,'Sales_sum');

Drop table Sales_sum;

Data:
LOAD Customer,
  Sales,
  AutoNumber(Sales/$(vSales_Sum)) as Rank,
Num(Sales/$(vSales_Sum), '#,##0.00%') as %Sales_Rank
Resident Temp

Order By Sales desc;

Drop table Temp;

Re: Any Similar function in qlikview as percent_rank in SQL ?

aniketsr — Wed, 24 Apr 2019 12:18:27 GMT

Hey Hi ,

Thank you for the prompt reply.
is there any way we can do it in expression .

i have to implement something like the below logic which is in sql into Qlikview Expression:
(PERCENT_RANK() over(partition by year,Flag,Value order by Dim_Value asc))

Re: Any Similar function in qlikview as percent_rank in SQL ?

HirisH_V7 — Thu, 25 Apr 2019 05:50:34 GMT

can u send a sample data and your'e output required out of it.

(PERCENT_RANK() over(partition by year,Flag,Value order by Dim_Value asc))

Above can be written-ed something like this.

Load Key column, Rank% as Over Resident Table_Name Group by year,Flag,Value order by Dim_Value asc;

Re: Any Similar function in qlikview as percent_rank in SQL ?

aniketsr — Mon, 06 May 2019 12:45:52 GMT

Attached is the QVD with desired output :

We have the percentile value coming from database using :
(PERCENT_RANK() over(partition by ID order by Value asc))

We have to calculate percentile in expression. but as of now the percentile is calculated in database.

Re: Any Similar function in qlikview as percent_rank in SQL ?

sunny_talwar — Mon, 06 May 2019 13:01:14 GMT

May be something like this?

(10/(Count(TOTAL ID)-1)) * (Rank(-Sum(Value))-1)

Re: Any Similar function in qlikview as percent_rank in SQL ?

aniketsr — Thu, 09 May 2019 06:07:09 GMT

Hi Sunny ,

Thanks for your guidance i would like to understand the significance of -Sum(Value) (why did you negate it ) and then again -1 while calculating rank .

Regards,
Aniket

Re: Any Similar function in qlikview as percent_rank in SQL ?

sunny_talwar — Thu, 09 May 2019 10:38:35 GMT

It is used inside the Rank() function.... Rank assigns 1 to the biggest number... and I was trying to get 1 for the lowest number... in order to do that you can multiply your number with -1