topic Re: Rank for Company in QlikView

Rank for Company

rkpatelqlikview — Fri, 02 Mar 2018 07:09:49 GMT

Hi All,

Its very urgent for me.

How to give the rank to the Company based on their performance %.

If 2 companies having the same % then it should compare with their no of branches.

Company Brancehs KPI% Rank

ABC 3 55% 3

DEF 6 55% 2

GHI 2 75% 1

JKL 8 45% 4

If select any company "ABC", Rank is -> 3/4.

Please can any one help on this. Thanks in advance

Re: Rank for Company

qliksus — Fri, 02 Mar 2018 08:23:14 GMT

Maybe something like the below

IF( FINDONEOF(RANK(SUM(KPI%)),'-')>0,RANK(SUM(Brancehs)) ,RANK(SUM(KPI%)))

Re: Rank for Company

jerryyang756 — Fri, 02 Mar 2018 08:50:47 GMT

Thanks & Nice. A little correction. You have to calculate the Rank on Total

IF( FINDONEOF(RANK(TOTAL SUM(KPI%)),'-')>0,RANK(TOTAL SUM(Brancehs)) ,RANK(TOTAL SUM(KPI%)))

Re: Rank for Company

rkpatelqlikview — Fri, 02 Mar 2018 09:08:09 GMT

Thanks response. Please find the attached sample data. I tried with this formula.

I'm unable to see the result.

Re: Rank for Company

rkpatelqlikview — Fri, 02 Mar 2018 09:29:43 GMT

Thanks for your response Pritam

Re: Rank for Company

rkpatelqlikview — Fri, 02 Mar 2018 09:34:12 GMT

Thanks for your reply Susant

Re: Rank for Company

Qrishna — Fri, 02 Mar 2018 09:51:09 GMT

psa

Re: Rank for Company

rkpatelqlikview — Fri, 02 Mar 2018 10:46:16 GMT

Many Thanks for your help Krishna. Excellent, But when am implementing on this attached data its not working.

Re: Rank for Company

jerryyang756 — Fri, 02 Mar 2018 11:07:54 GMT

I have used it on your sample data. Please check it If the sorting is correct or not.

Re: Rank for Company

rkpatelqlikview — Fri, 02 Mar 2018 12:15:52 GMT

Thanks Pritam,

Yes, Correct one.If the % is same then it should count the trucks like below. Sum(Measure) is 100 and a100 for two carriers at that situation we can give the rank based on the no.of Trucks. at that time first one is Rank2, next one is Rank1.

I need to show this in the two text objects one for current rank and another for Previous rank.

Thanks

KUMAR

Re: Rank for Company

rkpatelqlikview — Sat, 03 Mar 2018 05:27:34 GMT

It is correct one. Thanks Pritam,

Can you please help me to understand this.

This is the expression for KPI %

=rangesum((Ceil(SUM({<[Control_Fleet (Y/N)]={'Yes'}>}Quantity)/SUM({<[Control_Fleet (Y/N)]={'Yes'}+{'No'}>}Quantity)*100,0.01,0) /4)

(Ceil(SUM({<[iVMS ( Y/N)]={'Yes'}>}Quantity)/SUM({<[iVMS ( Y/N)]={'Yes'}+{'No'}>}Quantity)*100,0.1,0)/4 )

(ceil(sum({<[Safe KM driven Classification]={'Green'}>} [Kms Travelled])/sum([Kms Travelled]) *100,0.1,0) /4 )

(round((Count(DISTINCT [Vehicle Number]) - Count(DISTINCT {<Status1 = {"$(='>=' & Date(Today() - 7))"}>} [Vehicle Number]))/Count (DISTINCT [Vehicle Number]) *100, 0.01,0)/4)).

How can i write below statement by using the above KPI???

=Aggr(rank(Sum( KPI% ) + [Vehicle Number],1), [Carrier Name]).

Thanks again for your support.