Compare Prices greater than

[Daniel1908]

Hello together, 

I have a table with 4 different prices. 

I would like to see only the material where Price 4 is greater than one of the other prices 1-3. 

Can you help to get this in set analysis?

Kind Regards, 
Daniel

[marcus_sommer]

I don't think that's possible within a classical set analysis but you could apply a normal condition - maybe with something like this:

-([Price 4] > rangemax([Price 1], [Price 2], [Price 3])) as PriceFlag

within the UI or like hinted as Flag within the data-model.

- Marcus

[MarcoWedel]

{$<Material={"=[Price 4]>[Price 1] or [Price 4]>[Price 2] or [Price 4]>[Price 3]"}>}

[marcus_sommer]

Yes, it could be written within a set analysis syntax but logically it's an if-loop.

- Marcus

[Daniel1908]

It works if Price4 is greater Price1, but unfortunately not if price4 is only bigger than Price2 or Price3. 

[marcus_sommer · Accepted solution]

Instead of rangemax() you could use rangemin() to check if it's greater than any of the other prices.

- Marcus

[Daniel1908]

Thanks Marcus, that worked

[Daniel1908]

Hello together, 

([Price 4] > rangemin([Price 1], [Price 2], [Price 3])) as PriceFlag works fine. 

Now I have the problem, that some prices are based on different quantities.
Is it possible to divide the 2 values and create a new column?

Material	Price 1	Qty Price 1	Price 2	Qty Price 2	Price 3	Qty Price 3	Price 4	Qty Price 4
Bike	1000	1	500	1	300	5	400	100

Kind Regards, 
Daniel

[marcus_sommer]

I think a dividing-approach could be technically done but won't work from a logically point of view. Thinking your approach further you will need in the end to match the quantity and the prices on a single level and then a more or less complex comparing-logic with if-loops.

Personally I would tend to a different approach by mapping the right price to each sales. This should be possible with a logic like an intervalmatch used. This means generating an interval-table with a from_quantity and to_quantity and the price. Then with a loop between them you will get a price for each possible quantity and this could be mapped to the sales.

The main-idea behind it is here very well explained: IntervalMatch - Qlik Community - 1464547.

- Marcus