# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
Contributor II

## Issue: Bottom 5 sales in pivot

Hi ,

i have to represent all the with bottom 5 sales in pivot table

where customer include some null  and blank values  & sales include negative and zero value

i have bottom 5 customers exclude customer with blank or null in customer name but negative and zero must be included

6 Replies
Contributor II
Author

For example

customer name , sales

A,10

B,-20

C,-5

D,0

E ,-3

F,0

G,20

output should include

B,-20

C,-5

D,0

E ,-3

F,0

Contributor II
Author

I used rank( total -sum (sales))

it gives the negative value but not able to capture 0 values

Specialist

@priyanka08  Not able to understand your exact requirement though, based on my brief understanding your formula works well. Please see the screen shot below.

MVP

Hi

Try like below in straight table,

Dim1: =Aggr(If(rank( total -sum (sales))<=5, rank( total -sum (sales))),[customer name])

Dim2: [customer name]

Exp: Sum(sales)

In presentation tab, uncheck the "Suppress Zero value" and try it

Thanks & Regards,
Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Contributor II
Author

I have 3 dimesion,

12 value have zero values, its giving 1-12 as rank

even i am using rank( total -sum(sales),4,0)

MVP

For continuous ranking,

you can try like below

https://community.qlik.com/t5/QlikView-Documents/Continuous-Ranking-no-missing-Rank/ta-p/1486407

for ur case, if its one dim - customer name, try like below

Div(Index('-' & Concat(DISTINCT TOTAL Num(Aggr(Rank(-sum(sales),1,1),[customer name]), '0000'), '-', -Aggr(-sum(sales), [customer name])),Num(Rank(total Sum(-sales),1,1), '0000')), 5) + 1

Thanks & Regards,
Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Tags
Community Browser