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

[YoussefBelloum]

Hi,

you forgot to put the formula

[rkpatelqlikview]

Hi Youssef,

AM very sorry for that. Please find the below formula for KPI and RAnk.

KPI% Formula

=((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({<[Safety Category]={'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))

Rank:

=aggr (rank(TOTAL 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({<[Safety Category]={'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)), 1),  [Carrier Name])

Thanks for your support

[YoussefBelloum · Accepted solution]

Try this:

Rank on a textbox:

=Aggr(

Rank(

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

+

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

+

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

+

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

), [Carrier Name])

[rkpatelqlikview]

Many Thanks Youssef, Really helped me a lot.

Can i get previous rank also of the CarrierName?

Please how can i do?

[YoussefBelloum]

what do you mean by previous Rank ?

example, you make a selection on the Carrier name and with the expression above you have the Rank 8

previous rank will give you 7 ?

[rkpatelqlikview]

for example the carrier name Previous rank is 6 based on the data and current rank is 2.

Till now we found the expression for Current rank so that its showing current rank, i.e., 2.

same time I need to show previous rank 6 as well.

[YoussefBelloum]

here is one example (with the data I created to give you the example above)

on the image image below, on the table, Data is sorted by the ranking (highest to lowest)

on the second image below, I choose DFHEY which has a rank=2

on this example, what is the previous rank value for you ? it is the rank of the first name on the listbox (on which the names are maybe sorted by alphabetical sorting, which not makes sense) or what ?

[rkpatelqlikview]

DFHEY which has a rank=2 because the value is 825 which is the second highest. If the same name  DFHEY next day the value is increased to 1000 which is the top value then rank is 1. At this time our text box will show the number 1.

along with one more text box which should show the previous rank that is 2 of the DFHEY  name.

[YoussefBelloum]

So you use another dimension which is Date field ?

and you select a Name and a Date ?