How to show RANK in Text object

[rkpatelqlikview]

Hi All,

Can anyone please explain how to show the rank in text object. When i selected the name in the selection box then the Current Rank  it should display in one Text Object and and previous rank in one more text object.

Below is the formula for ranking which is i used in Straight Table.

Thanks

KUMAR

[rkpatelqlikview]

Hi Youssef,

Not actually. Name only is the selection. I am thinking that can we store the Previous Rank to particular variable and call that variable in to the Text object?

[rkpatelqlikview]

Hi  Youssef,

Hope you are doing great!

Please help me on the below expression to display the Rank in the Text object.

=

Aggr(

rank(

(Round(sum({<[Safety Category]={'Green'}>} [Kms Travelled]) / sum([Kms Travelled]) *100)/4)

+(Round((COUNT(DISTINCT [DEVICE NO]) -COUNT([Device Number]))/COUNT(DISTINCT  [DEVICE NO]) *100 )/4 ) +

(Round(SUM({<controlled={'Yes'}>}Quantity)/SUM({<controlled={'Yes'}+{'No'}>}Quantity)*100)/4)

+

(Round(SUM({<[iVMS ( Y/N)]={'Yes'}>}Quantity)/SUM({< [iVMS ( Y/N)]={'Yes'}+{'No'}>}Quantity)*100)/4)+sum([Kms Travelled])/(1E10)

,4,1), [Carrier Name])

thanks in advance

[YoussefBelloum]

Hi,

everything is going well thank you

assuming all your calculations are correct iside your expression, try this:

=Aggr(

rank(

(Round(sum({<[Safety Category]={'Green'},[Carrier Name]>} [Kms Travelled]) / sum({<[Carrier Name]>}[Kms Travelled]) *100)/4)

+(Round((COUNT(DISTINCT {<[Carrier Name]>}[DEVICE NO]) -COUNT({<[Carrier Name]>}[Device Number]))/COUNT(DISTINCT  {<[Carrier Name]>}[DEVICE NO]) *100 )/4 ) +

(Round(SUM({<controlled={'Yes'},[Carrier Name]>}Quantity)/SUM({<controlled={'Yes'}+{'No'},[Carrier Name]>}Quantity)*100)/4)

+

(Round(SUM({<[iVMS ( Y/N)]={'Yes'},[Carrier Name]>}Quantity)/SUM({< [iVMS ( Y/N)]={'Yes'}+{'No'},[Carrier Name]>}Quantity)*100)/4)+sum({<[Carrier Name]>}[Kms Travelled])/(1E10)), [Carrier Name])

[rkpatelqlikview]

Excellent Youssuef,  many Thanks  for your great support.

[YoussefBelloum]

You're welcome Kumar

[rkpatelqlikview]

HI Youssuef,

Am able to show the rank in Text object. Meanwhile i need to show Denominator out of no. Carriername.

Ex: There are 15 Carrier Names. Out of 15, the Carrier Rank is 5/15. How can i show this Denominator  "15"

=Aggr(

rank(

(Round(sum({<[Safety Category]={'Green'},[Carrier Name]>} [Kms Travelled]) / sum({<[Carrier Name]>}[Kms Travelled]) *100)/4)

+(Round((COUNT(DISTINCT {<[Carrier Name]>}[DEVICE NO]) -COUNT({<[Carrier Name]>}[Device Number]))/COUNT(DISTINCT  {<[Carrier Name]>}[DEVICE NO]) *100 )/4 ) +

(Round(SUM({<controlled={'Yes'},[Carrier Name]>}Quantity)/SUM({<controlled={'Yes'}+{'No'},[Carrier Name]>}Quantity)*100)/4)

+

(Round(SUM({<[iVMS ( Y/N)]={'Yes'},[Carrier Name]>}Quantity)/SUM({< [iVMS ( Y/N)]={'Yes'}+{'No'},[Carrier Name]>}Quantity)*100)/4)+sum({<[Carrier Name]>}[Kms Travelled])/(1E10)), [Carrier Name])

Thanks for your support

[YoussefBelloum]

Hi,

try this:

=Aggr(

rank(

(Round(sum({<[Safety Category]={'Green'},[Carrier Name]>} [Kms Travelled]) / sum({<[Carrier Name]>}[Kms Travelled]) *100)/4)

+(Round((COUNT(DISTINCT {<[Carrier Name]>}[DEVICE NO]) -COUNT({<[Carrier Name]>}[Device Number]))/COUNT(DISTINCT  {<[Carrier Name]>}[DEVICE NO]) *100 )/4 ) +

(Round(SUM({<controlled={'Yes'},[Carrier Name]>}Quantity)/SUM({<controlled={'Yes'}+{'No'},[Carrier Name]>}Quantity)*100)/4)

+

(Round(SUM({<[iVMS ( Y/N)]={'Yes'},[Carrier Name]>}Quantity)/SUM({< [iVMS ( Y/N)]={'Yes'}+{'No'},[Carrier Name]>}Quantity)*100)/4)+sum({<[Carrier Name]>}[Kms Travelled])/(1E10)), [Carrier Name])&' / '&Count(total [Carrier Name])

[rkpatelqlikview]

Thanks Youssef,

Its working overall. But here i have 5 groups. If i select any CarrierName it should give the rank and outof how many carriers in that Group. It should compare with in the group. I used below one. Please correct it. Nominator Group Rank is coming but Denominator is not working.

=Aggr(

rank(

(Round(sum({<[Safety Category]={'Green'},[Carrier Name]>} [Kms Travelled]) / sum({<[Carrier Name]>}[Kms Travelled]) *100)/4)

+(Round((COUNT(DISTINCT {<[Carrier Name]>}[DEVICE NO]) -COUNT({<[Carrier Name]>}[Device Number]))/COUNT(DISTINCT  {<[Carrier Name]>}[DEVICE NO]) *100 )/4 ) +

(Round(SUM({<[Control_Fleet (Y/N)]={'Yes'},[Carrier Name]>}Quantity)/SUM({<[Control_Fleet (Y/N)]={'Yes'}+{'No'},[Carrier Name]>}Quantity)*100)/4)

+

(Round(SUM({<[iVMS ( Y/N)]={'Yes'},[Carrier Name]>}Quantity)/SUM({< [iVMS ( Y/N)]={'Yes'}+{'No'},[Carrier Name]>}Quantity)*100)/4)+sum({<[Carrier Name]>}[Kms Travelled])/(1E10)), Group,  [Carrier Name])&' / '&Count(total  {<[Carrier Name]>} Group)

[rkpatelqlikview]

Thanks Youssef, Got it.

[YoussefBelloum]

Sorry Kumar, I was going back to you to get more details..happy to see that is it working for you