topic Re: urgent help required on rank function in QlikView

urgent help required on rank function

Tue, 01 Dec 2015 11:57:24 GMT

Hi Experts,

i have the following expression:

NUM(RANK(

SUM({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Sales)

SUM ({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Quantity)))

there is no error in the expression.

i have 100 branches sales & quantity. i need to calculate the rank for every branch using above expression.

it ranks fine. but the problem is... if any branch has no sales, then the rank is giving null value.

how can i avoid null ranks. instead of this null, i need to replace with the last rank.

i tried with Rangemax(expr,0), but that doesnt help.

Pls give some suggestions to fix this.

Re: urgent help required on rank function

Kushal_Chawda — Tue, 01 Dec 2015 12:07:17 GMT

try below expression

=NUM(RANK(

alt(SUM({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Sales),0)

alt(SUM ({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Quantity),0)))

Re: urgent help required on rank function

swuehl — Tue, 01 Dec 2015 18:01:54 GMT

I think the issue is caused by the division by zero.

Assuming that your division always results in positive numbers, you need to replace the division result in case of missing records with zero to get a maximum rank number:

NUM(RANK(

ALT(

SUM({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Sales)

SUM ({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Quantity)

,0)

))

Re: urgent help required on rank function

jagan — Thu, 03 Dec 2015 03:53:17 GMT

Hi,

Try Alt() function like below, it will replace with the given value(0) if expression value is null

=NUM(RANK(

Alt(SUM({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Sales)

SUM ({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Quantity), 0)))

Hope this helps you.

Regards,

Jagan.