topic Re: Display value that is nearest another value in QlikView

Display value that is nearest another value

sifatnabil — Mon, 19 Sep 2016 09:50:10 GMT

Hi all,

I have a straight table with dimension = Date, and expression = Price1, Price2. The problem is, sometimes there are multiple values of Price2 for a single date. How can I display the value of Price2 that is nearest to Price1 in the expression?

The formula I'm using to get the minimum difference between Price1 and Price2 = min(fabs(Price1-Price2))

Re: Display value that is nearest another value

Anil_Babu_Samineni — Mon, 19 Sep 2016 09:57:07 GMT

Instead of fabs you can try Interval

Min(Interval(Price1 - Price2))

Can you provide sample and expected output with Inline Data

Re: Display value that is nearest another value

sifatnabil — Mon, 19 Sep 2016 10:04:17 GMT

Hi loveisfail‌, that returns the minimum difference, however, how can I display the Price1 and Price2 associated with that minimum difference? Here's some sample data:

I need to display the value of Price1 that corresponds to the minimum difference between Price1 and Price2 (I've highlighted in bold the price that should be displayed):

Product	Date	Price1	Price2
rws	01/06/2016	64.87;66.25	65.115
rws	02/06/2016	64.87;69.50	68.54357
rws	03/06/2016	64.87;69.50	68.54357
rws	06/06/2016	64.87;69.50	68.31633
rws	07/06/2016	64.87;70.50	69.615
rws	08/06/2016	64.87;71.50	72.115
rws	09/06/2016	64.87;74.50	72.115
rws	10/06/2016	64.87;73.50	74.32333
rws	13/06/2016	64.87;73.50	74.115
rws	14/06/2016	64.87;72.50	74.14967
rws	15/06/2016	64.87;74.00	71.615
rws	16/06/2016	64.87;74.00	73.615
rws	17/06/2016	64.87;72.50	73.615

Re: Display value that is nearest another value

sunny_talwar — Mon, 19 Sep 2016 10:31:34 GMT

How about this:

FirstSortedValue(Price1, Aggr(fabs(Price1-Price2), Date, Price1))

Re: Display value that is nearest another value

sunny_talwar — Mon, 19 Sep 2016 10:32:43 GMT

Or this:

FirstSortedValue(Aggr(Price1, Date, Price1), Aggr(fabs(Price1-Price2), Date, Price1))

Re: Display value that is nearest another value

sasikanth — Mon, 19 Sep 2016 10:42:54 GMT

Try this one too

Sum(If( Aggr( rank(-fabs(Price1-Price2)),Date,Price1)<=1, Price2))