Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
luiz_damascena
Contributor III
Contributor III

Show higher values AND smaller than current, using different columns

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.

1 Solution

Accepted Solutions
sunny_talwar

Check attached

Capture.PNG

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)))))

View solution in original post

16 Replies
sunny_talwar

Will you be elaborate on where exactly are you hoping to see 7 and 16? And it will be always Field 3 down and Field 4 up? or is there another way user are able to determine this?

Anonymous
Not applicable

Hi,

Something like this

Regards,

luiz_damascena
Contributor III
Contributor III
Author

I'll try to make the question clearer ...

Let us assume that Field1 is the product code (a car, for example), Field2 is a month, Field3 is the average consumption, Field4 is the highest speed achieved, Field5 is the longest distance traveled, etc.

That is, each field after Field2 are measured quantities relative to my car, in a given month.

Looking at the information of car 1 in March, I also want to see what the average consumption is closer to mine, up and down; at the same time, I want to know the highest speed, up and down, always considering all the cars in the same month.

I can not use classification. Assuming a possible index using Field1, the desired information (in relation to my average consumption, which is immediately below mine?) will not be in the "top line". It can be anywhere, in any line.

I return to the example of the maximum and minimum: displaying my speed, to know the highest and lowest speeds recorded, considering all vehicles, it would be enough to use max () and min ().

But I do not want the maximum value: I want the value closest to mine, which can be in any line.

I hope I have elaborated better ...

Thank you in advance.

Luiz Carlos

luiz_damascena
Contributor III
Contributor III
Author

I tried to elaborate a better question... please, read it.

luiz_damascena
Contributor III
Contributor III
Author

Interesting approach, Mauri, but not exactly what I want. But thank you! I tried to elaborate my question better... please, read it.

sunny_talwar

That is, each field after Field2 are measured quantities relative to my car, in a given month.

So, your car is not part of the data because everything is relative to your car?

Looking at the information of car 1 in March

Now car 1 is your car? but this is not part of the database or is it?

I am feeling lost, would you be able to share a sample or sample data and give out exactly what you are hoping to get out of it?

sasiparupudi1
Master III
Master III

Please attach a sample

luiz_damascena
Contributor III
Contributor III
Author

Right now, grateful for your attention and patience ...

"My car" is part of the data. Each car in the database has its own information.

Here's a sample data:

Fields

carCode, monitoredMonth, averageConsumption, highestSpeed​, distanceTraveled

Values

1, Jan, 14.2, 100, 240

2, Jan,  7.7,  85, 400

3, Jan, 12.1, 120, 200

4, Jan,  8.5,  92, 130

5, Jan, 10.1,  70, 350

As extra information, the units of measure of the columns: averageConsumption km / l, highestSpeed ​​km / h, distanceTraveled km.

Seeing information from car 3, I see that averageConsumption is 12.1 km / l, highestSpeed 120 km / h, distanceTraveled 200 km.

Also, I want to display the averageConsumption immediately lower than that of car 3, 12.1 Km / l, which will be on the screen. The answer will be 10.1 km / l (information linked to car 5)

I also want to show the distance traveled immediately greater than that of the car, 200 Km, which will be on the screen. The answer will be 240 km (information linked to car 1).

sunny_talwar

This?

Capture.PNG