topic Compare Prices greater than in QlikView

Compare Prices greater than

Daniel1908 — Wed, 23 Feb 2022 12:50:39 GMT

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

Re: Compare Prices greater than

marcus_sommer — Wed, 23 Feb 2022 13:50:49 GMT

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

Re: Compare Prices greater than

MarcoWedel — Wed, 23 Feb 2022 19:55:14 GMT

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

Re: Compare Prices greater than

marcus_sommer — Thu, 24 Feb 2022 06:06:33 GMT

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

- Marcus

Re: Compare Prices greater than

Daniel1908 — Thu, 24 Feb 2022 17:05:43 GMT

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

Re: Compare Prices greater than

marcus_sommer — Thu, 24 Feb 2022 17:42:36 GMT

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

- Marcus

Re: Compare Prices greater than

Daniel1908 — Fri, 25 Feb 2022 07:57:35 GMT

Thanks Marcus, that worked

Re: Compare Prices greater than

Daniel1908 — Fri, 11 Mar 2022 13:47:12 GMT

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

Re: Compare Prices greater than

marcus_sommer — Fri, 11 Mar 2022 15:05:39 GMT

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