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)
18 Replies
ingoniclas
Creator II
Creator II
Author

Sorry, I know this is hard to understand 😞  I didn*t come up with this rules. The best I can think of to explain what is required is taking you back to the examples from above.

If there are exactly 4 products with the same price then these are the '4 cheaptest'. However, if there are less than four products with the same cheapest price then the next group of products with the second lowest price are included and so on...

Sorry, I really don't have a better explanation.

Examples_four_cheapest.JPG

sunny_talwar

I understand the explanation part, all I need is a mocked up data to test a solution which will work for you.

ingoniclas
Creator II
Creator II
Author

I see. Here you go.

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

ingoniclas
Creator II
Creator II
Author

Hi Sunny,

thanks for your suggestions. However, my result looks different, see below. No idea why!?

And there is another challenge: I need to use the if...else clause in the object, not in the script, since primarily a subgroup will be selected from which the 4 cheapest need to be determined ad hoc... 😞

4 cheapest.JPG

 

 

sunny_talwar

1) It has to do with the formatting, I am not sure how exactly is Price formatted which is why you don't see a result

2) What did I change in the script? I mentioned a variable... but nothing  changed in the script... my script was just this?

image.png

ingoniclas
Creator II
Creator II
Author

I thought you defined a variable like this: 

SET vCheck=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)));

If not, how did you do it?

sunny_talwar

Added it directly on the front end within variable overview

image.png

 

ingoniclas
Creator II
Creator II
Author

I had to adapt it to my needs a little bit, but now it works like a charm! Many thanks, Sunny! Great solution!