Announcements
cancel
Showing results for
Did you mean:
Not applicable

## urgent help required on rank function

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.

1 Solution

Accepted Solutions
MVP

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)

))

3 Replies

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

MVP

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)

))

Luminary Alumni

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.

Community Browser