topic Re: Tricky - Defining the 'four cheapest' products in QlikView

Tricky - Defining the 'four cheapest' products

ingoniclas — Sat, 16 Nov 2024 01:54:27 GMT

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:

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

Re: Tricky - Defining the 'four cheapest' products

sunny_talwar — Tue, 19 Nov 2019 12:02:11 GMT

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

Re: Tricky - Defining the 'four cheapest' products

ingoniclas — Tue, 19 Nov 2019 15:42:29 GMT

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

Re: Tricky - Defining the 'four cheapest' products

sunny_talwar — Tue, 19 Nov 2019 18:50:31 GMT

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

Re: Tricky - Defining the 'four cheapest' products

ingoniclas — Tue, 19 Nov 2019 19:02:05 GMT

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

Re: Tricky - Defining the 'four cheapest' products

sunny_talwar — Tue, 19 Nov 2019 19:17:13 GMT

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

Re: Tricky - Defining the 'four cheapest' products

ingoniclas — Tue, 19 Nov 2019 19:41:20 GMT

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?

Re: Tricky - Defining the 'four cheapest' products

sunny_talwar — Tue, 19 Nov 2019 19:45:06 GMT

Why would you not include 00273689?

Re: Tricky - Defining the 'four cheapest' products

ingoniclas — Tue, 19 Nov 2019 19:53:34 GMT

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

Re: Tricky - Defining the 'four cheapest' products

sunny_talwar — Tue, 19 Nov 2019 19:55:34 GMT

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.

Re: Tricky - Defining the 'four cheapest' products

ingoniclas — Tue, 19 Nov 2019 20:11:22 GMT

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.

Re: Tricky - Defining the 'four cheapest' products

sunny_talwar — Tue, 19 Nov 2019 20:13:00 GMT

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

Re: Tricky - Defining the 'four cheapest' products

ingoniclas — Tue, 19 Nov 2019 20:24:03 GMT

I see. Here you go.

Re: Tricky - Defining the 'four cheapest' products

sunny_talwar — Tue, 19 Nov 2019 20:45:47 GMT

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

Re: Tricky - Defining the 'four cheapest' products

ingoniclas — Wed, 20 Nov 2019 15:45:31 GMT

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... 😞

Re: Tricky - Defining the 'four cheapest' products

sunny_talwar — Wed, 20 Nov 2019 15:53:19 GMT

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?

Re: Tricky - Defining the 'four cheapest' products

ingoniclas — Wed, 20 Nov 2019 15:58:05 GMT

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?

Re: Tricky - Defining the 'four cheapest' products

sunny_talwar — Wed, 20 Nov 2019 16:31:31 GMT

Added it directly on the front end within variable overview

Re: Tricky - Defining the 'four cheapest' products

ingoniclas — Thu, 21 Nov 2019 15:12:27 GMT

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