topic Re: Ranking which based on variance in QlikView

Ranking which based on variance

Srinivas — Wed, 02 Dec 2015 18:11:20 GMT

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

Re: Ranking which based on variance

Clever_Anjos — Wed, 02 Dec 2015 18:32:51 GMT

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

Re: Ranking which based on variance

Gysbert_Wassenaar — Wed, 02 Dec 2015 18:35:35 GMT

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;

Re: Ranking which based on variance

Srinivas — Thu, 03 Dec 2015 01:41:50 GMT

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

Re: Ranking which based on variance

jonathandienst — Thu, 03 Dec 2015 05:24:46 GMT

The suggestion from cleveranjos seems to work correctly:

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

This is the result in your table: