how to show the Previous Rank in straight table.

[rkpatelqlikview]

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.

[rkpatelqlikview]

Hi sunny,

Once you open the Qvw.There two sheets.

1) One thing In first show there is Straight table in that i need to provide the one more column "Previous Rank"

2) Another thing in second sheet Text objects i have given the rank for Overall and Group in Numerator   and Denominator i need to display out of how many numbers.

For example: In class there 3 sections. section A having 10 students. B -15, C-20.

Student NAME X belong to  Section A  then rank is 12 / 10 (it should show overall students) .like this  for all.

[sunny_talwar · Accepted solution]

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

[sunny_talwar]

For example: In class there 3 sections. section A having 10 students. B -15, C-20.

Student NAME X belong to  Section A  then rank is 12 / 10 (it should show overall students) .like this  for all.

Can you give an example with the context of the sample provided....

[rkpatelqlikview]

Thanks Sunny,

Please find attached QVW. I have given the detailed explanation.

Thanks again

[sunny_talwar]

Checking... did the Previous Rank work or not?

[sunny_talwar]

Not entirely sure, but may be this

Group Rank

=Count(DISTINCT {<[Carrier Name], Plant = p(Plant)>}[Carrier Name])

Overall Rank

=Count(DISTINCT {<[Carrier Name], Plant = p(Plant), Group>}[Carrier Name])

[rkpatelqlikview]

Thanks for your efforts sunny. done great job. . Its working.

[rkpatelqlikview]

Excellent sunny, Problem with Group Rank. Can you please suggest?. Both giving same output

OverAll Rank  -> Working fine

=Count(DISTINCT {<[Carrier Name], Plant = p(Plant)>}[Carrier Name])

Group Rank

=Count(DISTINCT {<[Carrier Name], Plant = p(Plant), Group>}[Carrier Name])

[sunny_talwar]

Based on selections, I am getting different numbers... what is different in your selections?

[rkpatelqlikview]

Thanks Sunny, this is also done. Working now with P(Group).

Group Rank

=Count(DISTINCT {<[Carrier Name], Plant = p(Plant), Group=P(Group)>}[Carrier Name])