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.

[bc-thebruuu]

Are you saying that user can do selection by month, year or quarter?

And what if someone select month AND quarter?

You should allow one possible type of selection

and then you should have 3 different formulas based on the type of selection the user did

[rkpatelqlikview]

Yes. User will select only one.Either month or Quarter. what are those 3 different formulas?

[bc-thebruuu]

if(GetSelectedCount([MonthYear])=1,

//formula 1 based on MonthYear selection

Aggr(

rank(RangeSum(

(Round(MonthYear=....,

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

..)

,

if(GetSelectedCount([Year])=1,

//formula 2 based on Year selection

Aggr(

rank(RangeSum(

(Round(Year=....,

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

..),

if(GetSelectedCount([Year])=1,

/formula 2 based on Quarter selection

Aggr(

rank(RangeSum(

(Round(Quarter=....,

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

..),

else default way of ranking

)

[rkpatelqlikview]

Thanks Bruno,

Below one i implemented. Is this correct? Its showing null values. Can you please check it once?

=if(GetSelectedCount([MonthYear])=1,

Aggr(

rank(RangeSum(

(Round(MonthYear=

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({<[Control_Fleet (Y/N)]={'Yes'}>}Quantity)/SUM({<[Control_Fleet (Y/N)]={'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])

,if(GetSelectedCount([Year])=1,

Aggr(

rank(RangeSum(

(Round(Year=

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({<[Control_Fleet (Y/N)]={'Yes'}>}Quantity)/SUM({<[Control_Fleet (Y/N)]={'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]),

if(GetSelectedCount([Year])=1,

Aggr(

rank(RangeSum(

(Round(Quarter=

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({<[Control_Fleet (Y/N)]={'Yes'}>}Quantity)/SUM({<[Control_Fleet (Y/N)]={'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

[rkpatelqlikview]

Please find the data updated. Here we can show the current month Rank and previous rank.

[rkpatelqlikview]

Hi Sunny, thanks for your advice to all

I shared QVW. It's not been sorted out. Need to show  Current month and Previous Month in Straight Table.

[bc-thebruuu]

I 'll look @ it

[rkpatelqlikview]

Thanks for your response Bruno..I tried with your suggested expression.

Its showing nulls. Just i need Last month or current month Rank. Thanks for your support

Appreciate

[rkpatelqlikview]

Hi Bruno/ Sunny,

Can you please find with attached QVW? The Rank Previous Rank and there is a one more sheet i displayed the Rank in Text object but there is a denominator. Pleas open this. Thanks for your support.

[sunny_talwar]

I am not sure I understand... where is previous rank expression? What exactly are we trying to fix now? and what tab?