Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Any Similar function in qlikview as percent_rank in SQL.
May be something like this?
(10/(Count(TOTAL ID)-1)) * (Rank(-Sum(Value))-1)
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;
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.
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.
May be something like this?
(10/(Count(TOTAL ID)-1)) * (Rank(-Sum(Value))-1)
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