urgent help required on rank function

[#1]

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.

[Kushal_Chawda]

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

[swuehl · Accepted solution]

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)

))

[jagan]

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.