- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.