Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
Instead of fabs you can try Interval
Min(Interval(Price1 - Price2))
Can you provide sample and expected output with Inline Data
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 |
How about this:
FirstSortedValue(Price1, Aggr(fabs(Price1-Price2), Date, Price1))
Or this:
FirstSortedValue(Aggr(Price1, Date, Price1), Aggr(fabs(Price1-Price2), Date, Price1))
Try this one too
Sum(If( Aggr( rank(-fabs(Price1-Price2)),Date,Price1)<=1, Price2))