Ranking which based on variance

[Srinivas]

Hi All,

How will assign ranking with based on expression (Variance),  i have given rank below screen shot way. but i need reverse like please look into second screenshot.

.

I need above way Please help me ASAP.

Advance Thanks,

Muna

[Clever_Anjos]

please try Num(Rank(TOTAL-(Column(2)-Column(1)),1))

[Gysbert_Wassenaar · Accepted solution]

I don't think you can use the rank function to get the result you want. Try rank( sum(Qty)-sum(Sales),0,1). Perhaps the result of that is satisfactory. Otherwise you may have to calculate this in the script:

Date:

LOAD Date,

ID,

if(Sales<='500','30-Nov',

if(Sales>='500', '31-Oct')) as Monthend,

Year(Date) as Year,

Month(Date) as Month,

Qty,

Sales,

Sales-Qty as Variance

FROM (ooxml, embedded labels, table is Sheet1);

Result:

Noconcatenate

LOAD *, Autonumber(Variance) as Rank

Resident Result

Order by Variance;

Drop Table Date;

[Srinivas]

Hi Gysbert,

As per your suggestion i did in back-end edit script level that is coming correct, can we do in front end at straight table level,

I need in front end level,

Advance Thanks,

Muna

[jonathandienst]

The suggestion from cleveranjos seems to work correctly:

=Num(Rank(TOTAL -(Column(2)-Column(1)),1))

This is the result in your table: