topic Re: Ranking with quartiles in QlikView

Ranking with quartiles

sabmaldun — Sat, 16 Nov 2024 21:23:49 GMT

Hello,

I am quite new to Qlik. I Need to create a Ranking table of Customers according to Salesamount.

Customers should be ranked into first 10 and Bottom 10 percent. The left customers should be devided into 1. and 2. Quartil.

The table should look like this in the end:

Rank RankMapping CustomerID

1 First 10% 129875

2 First 10% 023948

10 1. Quartil 023455

80 2.Quartil 029354

100 Bottom 10% 485895

Thank you!

Re: Ranking with quartiles

pradosh_thakur — Sun, 17 Feb 2019 06:26:22 GMT

May be you need the below

Have a look at the article and it's related articles. Hopefully it helps.

https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-a-Pareto-Analysis-Revisited/ba-p/1473684

Re: Ranking with quartiles

sabmaldun — Mon, 18 Feb 2019 19:03:15 GMT

I think it should be solved somehow by a rank function, but I don't know how to do the middle quartiles

Re: Ranking with quartiles

sabmaldun — Mon, 18 Feb 2019 19:17:54 GMT

SOMETHING SIMILAR TO THIS BUT WITH top top 10% ,1.quartil 2.quartil, Bottom 10%

=Avg( TOTAL<quarter> Aggr( If( Rank( Sum(result),4,0) <= Count(TOTAL<quarter>result) / 10, Sum(result) ), quarter, key))

Re: Ranking with quartiles

sabmaldun — Mon, 18 Feb 2019 19:21:16 GMT

To make it clearer it should look like this in the end

Rank RankMapping CustomerID

1 First 10% 129875

2 First 10% 023948

...

10 1. Quartil 023455

11 1. Quartil 023655

...

80 2.Quartil 029354

81 2.Quartil 029356

...

100 Bottom 10% 485895

101 Bottom 10% 485795

Re: Ranking with quartiles

sabmaldun — Mon, 18 Feb 2019 19:25:21 GMT

so it is something like

if rank <10% then "First10%"

else if rank>10% and <50% then "1.Quartil"

else if rank>50% and <90% then "2.Quartil"

else Bottom 10%

But I dont know the exact Expression in QlikView.

Anyone knows how it works?

Thank you