Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikuser222
Creator
Creator

Rank Function ?

Hi Everyone,

I have a requirement ,how can we use rank function to sort the column in straight table alphabetically  A to Z( but suppressing where expressions amounts are  0 and null) and have a "* "in just first row?

PFA which explains the requirement clearly.

 

Thanks

Labels (3)
2 Solutions

Accepted Solutions
sunny_talwar

This should do it

If(Num(Rank(TOTAL Only({<Name = {"=Sum(Amt1) + Sum(Amt2) > 0"}>}Name&Task), 4, 1)) = Count(DISTINCT TOTAL {<Name = {"=Sum(Amt1) + Sum(Amt2) > 0"}>} Name&Task), '*')

View solution in original post

13 Replies
sunny_talwar

Is this what you are looking to get?

NameAmt1Amt2
*  
BAB1020
BCC79
CCC239
DDD100200
qlikuser222
Creator
Creator
Author

Thanks

 

It should be like below

 

Name Amt1   Amt2   4thcolumn

BAB     10            20         *

BCC     7               9       

CCC     23            9       

DDD     100       200       

 

Thanks

sunny_talwar

This?

image.png

qlikuser222
Creator
Creator
Author

Hi,

 

Thanks alot for earlier solution.

 

Can you please help me to understand how to make it work we introduce one more dimension in the table

PFA for more details

 

Thanks 

sunny_talwar

Would you want to see 2 * or 1 * and where?

qlikuser222
Creator
Creator
Author

Thanks

 

BAB has Market and Sales and i want to see * on the first row ( just 1)

 

example  first row   

BAB     Markets       10         20           *

sunny_talwar

This should do it

If(Num(Rank(TOTAL Only({<Name = {"=Sum(Amt1) + Sum(Amt2) > 0"}>}Name&Task), 4, 1)) = Count(DISTINCT TOTAL {<Name = {"=Sum(Amt1) + Sum(Amt2) > 0"}>} Name&Task), '*')
qlikuser222
Creator
Creator
Author

Thanks its working.

Can you please tell me why you used 4,1?
sunny_talwar

1 is not required, but 4 is used to give it distinct rank... that might not be needed as well because I added Task to the Only() function