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: 
ingoniclas
Creator II
Creator II

Tricky - Defining the 'four cheapest' products

Hi there,

I am facing a very special challenge: I need to develop a solution where the among a long list of products the "four cheapest" products will be determined. Here are examples to explain how the "four cheapest" are defined:

Examples_four_cheapest.JPG

 

 

 

 

 

 

I tried approaches with min-, FirstSortedValue- and rank formulas, but didn't quite solve this. Now I appreciate your ideas. Thanks.

Labels (6)
1 Solution

Accepted Solutions
sunny_talwar

Create a variable which decides what is the smallest number of min price which will take you to at least 4 products. I call the variable vCheck (feel free to call it something else)

If(Count({<Price = {"$(=Min(Price))"}>}Price) >= 4, 1,
If(Count({<Price = {"<=$(=Min(Price, 2))"}>}Price) >= 4, 2,
If(Count({<Price = {"<=$(=Min(Price, 3))"}>}Price) >= 4, 3, 4)))

Now you can use this in your set analysis

{<Price = {"<=$(=Min(Price, $(vCheck)))"}>}

Example

image.png

View solution in original post

18 Replies
sunny_talwar

I believe FirstSortedValue should work... can you share what you tried?

ingoniclas
Creator II
Creator II
Author

Hi Sunny,

the problem with firstsortedvalue is that it generates a NULL value when there is more than one product with the same price, that's why I dumped this approach ( I don't have the scripting anymore). One can overcome this by adding distinct, but then all other products with the same price get kicked out of the definition of the 4 cheapest products...

Ingo

 

 

sunny_talwar

So what would you like to see when there are multiple products? Concat them all?

ingoniclas
Creator II
Creator II
Author

Well yes, according to the definition of the 'four cheaptest'.

sunny_talwar

May be something like this... this is a very general formula because I don't really know how your data looks like... I am making a lot of assumptions here which may or may not be true (like there is only one price per product)

Concat(Aggr(If(Price = Min(TOTAL Price), Product), Product))

ingoniclas
Creator II
Creator II
Author

Hi Sunny,

unfortunately, this suggestion doesn*t take into account that there might be products with exactly the same price. See a real example below where I tried to make the background colour yellow for those products belonging to the group of the 'four cheapest' with the following formula:

=if(Price=min(total Price, 1), yellow(), if(Price=min(total Price, 2), yellow(), if(Price=min(total Price, 3), yellow(), if(Price=min(total Price, 4), yellow()))))

However, the products with the prices that actually should be in yellow are encircled in red. Do you see what I need?

Examples_four_cheapest_3.jpg

 

sunny_talwar

Why would you not include 00273689?

ingoniclas
Creator II
Creator II
Author

Exactly, as defined in the examples in the table above. If every product had a different price this would be an easy game.

sunny_talwar

Helping you seems like I am trying to solve a riddle :). Would you be able to put a small mock Excel file so that we can see the kind of data you have and clearly lay out the output you are looking to get from it.