Skip to main content

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 
Search instead for 
Did you mean: 
priyanka08
Contributor II
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
priyanka08
Contributor II
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

 

 

 

priyanka08
Contributor II
Contributor II
Author

I used rank( total -sum (sales))

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

sidhiq91
Specialist
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. 

sidhiq91_0-1664242417655.png

 

MayilVahanan

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.
priyanka08
Contributor II
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)

MayilVahanan

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.