Show higher values AND smaller than current, using different columns

[luiz_damascena]

Imagine the following scenario: a set of records as below

Field1, Field2, Field3, Field4

     1,      2,      3,      4

     5,      6,      7,      8

     9,     10,     11,     12

    13,     14,     15,     16

    17,     18,     19,     20

With the applied filters, the information visualized is from the third line: 9,10,11,12

I want to know, among all the values in my recordset,

• What is the next value down from the Field 3 column. The answer would be 7.
• What is the next value up from the Field 4 column. The answer would be 16.

Answers will be displayed in the same query, without changing my current filtering.

It is as if I wanted to display the current value and also the maximum value using the Max() function.

But I want to display the next higher or lower value for different columns, as the case may be.

Is it possible?

Thank you in advance,

Luiz Carlos.

[Mark_Little]

HI

I would use peek

So something Like

ABOVE:

LOAD

     ROWNO()          AS Order,

     FIELD1,

     PEEK(FIELD,-1)      as ABOVEFIELD1,

..

Then reload the Table is reverse order to get the below.

NOCONCATENATE

BELOW:

LOAD

     Order ,

     FIELD

     PEEK(FIELD,-1)      as BELOWFIELD1

     ...

Resident ABOVE

Order by Order desc;

Mark

[luiz_damascena]

Almost there!!!

First of all, the expression you created is scary ... lol

When you select the car, it shows the car number that meets the condition - and I want to show the value read.

For example: Selecting car 3, is shown in Closet Field A Car number 5. But I want to show the value that is in this field, for car 5. In the case, 10.1.

But I could not adjust your expression ...

Thank you in advance.

[sunny_talwar · Accepted solution]

Check attached

Changed this

Pick(Match(RangeMin(Fabs(FieldA - Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc))))), fabs(FieldA - Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))),

Fabs(FieldA - Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc))))), fabs(FieldA - Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))),

Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))), Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))

[sunny_talwar]

If doing this in the script is an option, i.e., this information remain static and doesn't change based on selection, I would do it the way mark6505‌ has pointed out

[luiz_damascena]

Perfect!

I'll check the possibility of doing it via script, but what I wanted, your expression does!

Now it is to understand more and adapt to my data model ...

But, thank you, again.

Luiz Carlos.

[sunny_talwar]

Let me break it down a little

Step1 - Calculate the difference between Current Car - Value Above and Value Below

Pick(Match(RangeMin(Fabs(FieldA - Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc))))), fabs(FieldA - Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))),

Fabs(FieldA - Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc))))), fabs(FieldA - Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))),

Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))), Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))

In this step, you can probably use set analysis instead of using Aggr() and Above() which will make your expression perform better... but if we continue with this

Step2 - find the absolute value of the difference

Pick(Match(RangeMin(Fabs(FieldA - Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc))))), fabs(FieldA - Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))),

Fabs(FieldA - Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc))))), fabs(FieldA - Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))),

Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))), Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))

Step3 - find the min difference

Pick(Match(RangeMin(Fabs(FieldA - Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc))))), fabs(FieldA - Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))),

Fabs(FieldA - Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc))))), fabs(FieldA - Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))),

Only({1} Aggr(Above(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))), Only({1} Aggr(Below(Only({1} FieldA)), Month, (FieldA, (NUMERIC, asc)))))

Step4 - Find the value associated with the minimum difference...

Pick(Match(MinAbsoluteDifference, AbsoluteDifference1, AbsoluteDifference2), Value1, Value2)

So if MinAbsoluteDifference = AbsoluteDifference1, then pick Value1, if MinAbsoluteDifference = AbsoluteDifference2, then pick Value2

I hope this will make it somewhat easy to understand.

Best,

Sunny

[luiz_damascena]

Thanks a lot, Sunny!