Announcements
cancel
Showing results for
Did you mean:
Creator III

## how to show the Previous Rank in straight table.

Hi Experts,

Below is my expression in in straight table for Rank.

=

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),Group, [Carrier Name])

If i select this month in other expression it should be Previous Rank..

1 Solution

Accepted Solutions
MVP

Try this for rank

=Aggr(

rank(RangeSum(

(Round(sum({<[Safety Category]={'Green'}, MonthYear = {"\$(=Date(MonthStart(Max(MonthYear), -1), 'MMM-YYYY'))"}, Month>} [Kms Travelled]) / sum({<MonthYear = {"\$(=Date(MonthStart(Max(MonthYear), -1), 'MMM-YYYY'))"}, Month>}[Kms Travelled]) *100)/4),

(Round(((Count(DISTINCT{<Datums=, Month>}[DEVICE NO]) - COUNT({<Datums={"\$(=Date(MonthEnd(Max(Datums), -1)))"}, Month>} DISTINCT [Device Number]))

/Count(DISTINCT{<Datums=, Month>}[DEVICE NO]) ) *100)/4 ),

(Round(SUM({<[Control_Fleet (Y/N)]={'Yes'}, MonthYear = {"\$(=Date(MonthStart(Max(MonthYear), -1), 'MMM-YYYY'))"}, Month>}Quantity)/SUM({<[Control_Fleet (Y/N)]={'Yes'}+{'No'}, MonthYear = {"\$(=Date(MonthStart(Max(MonthYear), -1), 'MMM-YYYY'))"}, Month>}Quantity)*100)/4),

(Round(SUM({<[iVMS ( Y/N)]={'Yes'}, MonthYear = {"\$(=Date(MonthStart(Max(MonthYear), -1), 'MMM-YYYY'))"}, Month>}Quantity)/SUM({< [iVMS ( Y/N)]={'Yes'}+{'No'}, MonthYear = {"\$(=Date(MonthStart(Max(MonthYear), -1), 'MMM-YYYY'))"}, Month>}Quantity)*100)/4)+sum({<MonthYear = {"\$(=Date(MonthStart(Max(MonthYear), -1), 'MMM-YYYY'))"}, Month>}[Kms Travelled])/(1E10)

,4,1)), [Carrier Name])

42 Replies
MVP

Is there a sample you would be able to share you help you better here?

Creator

Usually rank function needs TOTAL to work properly

Creator III
Author

Thanks Sunny and Bruno,

Please find the attached and There is total points but Rank is getting Nulls instead of ranking. I need to show that rank as well.

Creator

The point is that sum([Kms Travelled]) is null.

Your rank function looks good but it cannot rank 'divided by zero' values

What do you expect to happen when your rank function elements are divided by zero?

You have to use Rangesum probably

instead of rank of (sum...+ sum+ sum) you have to use rank(rangesum(sum1, sum2, sum3)

Remember that 1+ null= null

MVP

Try using RangeSum() instead of + just like you did for Total Points

MVP

Also suggest using formatting from number's tab rather than using '%' for your KPI expression... did the change from KPI1 and KPI2

Creator III
Author

Thanks Sunny. Will check it out accordingly.

Creator III
Author

Thats fine Sunny.

How to show the last Rank of that particular CarrierName?

there is a Expression name Previous rank?

Please may i know how to do?

Creator III
Author

Many thanks Bruno.

How to show the last Rank of that particular CarrierName?

there is a Expression name Previous rank?

Please may i know how to do?

Community Browser