Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
I understand the explanation part, all I need is a mocked up data to test a solution which will work for you.
I see. Here you go.
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
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... 😞
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?
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?
Added it directly on the front end within variable overview
I had to adapt it to my needs a little bit, but now it works like a charm! Many thanks, Sunny! Great solution!