Any Similar function in qlikview as percent_rank in SQL ?

[aniketsr]

Hi,

Any Similar function in qlikview as percent_rank in SQL.

[HirisH_V7]

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;

[aniketsr]

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

[HirisH_V7]

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;

[HirisH_V7]

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

[aniketsr]

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.

[sunny_talwar · Accepted solution]

May be something like this?

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

 

[aniketsr]

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

[sunny_talwar]

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