Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
ingoniclas
Contributor

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.

1 Solution

Accepted Solutions
Highlighted

Re: Tricky - Defining the 'four cheapest' products

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
Highlighted

Re: Tricky - Defining the 'four cheapest' products

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

Highlighted
ingoniclas
Contributor

Re: Tricky - Defining the 'four cheapest' products

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

 

 

Highlighted

Re: Tricky - Defining the 'four cheapest' products

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

Highlighted
ingoniclas
Contributor

Re: Tricky - Defining the 'four cheapest' products

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

Highlighted

Re: Tricky - Defining the 'four cheapest' products

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))

Highlighted
ingoniclas
Contributor

Re: Tricky - Defining the 'four cheapest' products

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

 

Highlighted

Re: Tricky - Defining the 'four cheapest' products

Why would you not include 00273689?

Highlighted
ingoniclas
Contributor

Re: Tricky - Defining the 'four cheapest' products

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

Highlighted

Re: Tricky - Defining the 'four cheapest' products

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.