Qlik Community

New to Qlik Sense

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

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Willatsch
Contributor II
Contributor II

Max Rank

Hello guys I have a problem with my set expression. 

What I have is a set expression to filter from a big data source: Max({1<Customer ={"1"}>*1<Product ={"Laptop"}>}[Price])

This way I can filter the customer I need to show in my analysis with the specific product showing the maximum price that was paid. But what I also want is to show the second highest price that was paid. I know it works with a rank and a number. I just dont know where to put the rank in my syntax. Can somebody help me?

 

Kind regards

2 Solutions

Accepted Solutions
GaryGiles
Specialist
Specialist

The FirstSortedValue() function will allow you to specify the position of the sorted values.  So, in the expression below, it should return the 2nd highest value.

=FirstSortedValue({1<Customer ={"1"},Product ={"Laptop"}>} Distinct [Price],-[Price],2)

 

View solution in original post

swuehl
MVP
MVP

The Max() function also has a second, optional argument to request the nth top ranked value, not only the maximum.

Max({1<Customer ={"1"}>*1<Product ={"Laptop"}>}[Price] , 2 )

View solution in original post

4 Replies
GaryGiles
Specialist
Specialist

The FirstSortedValue() function will allow you to specify the position of the sorted values.  So, in the expression below, it should return the 2nd highest value.

=FirstSortedValue({1<Customer ={"1"},Product ={"Laptop"}>} Distinct [Price],-[Price],2)

 

View solution in original post

Willatsch
Contributor II
Contributor II
Author

Thank you very much!!

swuehl
MVP
MVP

The Max() function also has a second, optional argument to request the nth top ranked value, not only the maximum.

Max({1<Customer ={"1"}>*1<Product ={"Laptop"}>}[Price] , 2 )

View solution in original post

Willatsch
Contributor II
Contributor II
Author

Hallo Swuehl, 

I discovered that feature while playing around with the syntax that was given to me by GaryGiles. Both of you helped me a lot to understand the set expression better.