Solved: how to show the Previous Rank in straight table. - Qlik Community

rkpatelqlikview · ‎2018-04-26

Hi Experts,

Please help on this.

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..

Please let me know if you want more info.

sunny_talwar · ‎2018-04-30

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])

View solution in original post

sunny_talwar · ‎2018-04-26

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

bc-thebruuu · ‎2018-04-26

Usually rank function needs TOTAL to work properly

rkpatelqlikview · ‎2018-04-26

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.

bc-thebruuu · ‎2018-04-26

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

sunny_talwar · ‎2018-04-26

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

sunny_talwar · ‎2018-04-26

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

rkpatelqlikview · ‎2018-04-26

Thanks Sunny. Will check it out accordingly.

rkpatelqlikview · ‎2018-04-26

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?

rkpatelqlikview · ‎2018-04-26

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?