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.
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
I believe FirstSortedValue should work... can you share what you tried?
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
So what would you like to see when there are multiple products? Concat them all?
Well yes, according to the definition of the 'four cheaptest'.
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))
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?
Why would you not include 00273689?
Exactly, as defined in the examples in the table above. If every product had a different price this would be an easy game.
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.