Filter. Min value or/and all except certain values

[#1]

Good day,

I need help with using filters. In my case, I have a chart with X-axis Amount and Y-axis Price. I sorted Amount by Price. But now, I want to filter my data by:

1) showing the only amount of minimum price for each company separately. For example, it would be 8/12 for Company B, 10/22 for Company A and 20/63 Company C.

2) ability to choose certain values or all values except certain. For example, display each company's first 2 lowest price amounts (Company B 8/12 and 10/36; Company A 10/22 and 15/30; Company C 20/63).

Message was edited by: Vidmat Vidmat

[swuehl]

Price and Amount are unique values per Company, so there is no aggregation involved in UI, right?

And Amount values are distinct, a single Amount is related to only one Company, right?

Then you could create a button with a Selection - Select in Field action with field

Amount

and search string

=Aggr(Rank(-Price)=1,Company,Amount)

[edit: replace =1 with <=2 etc. to select not min value, but lowest two values etc.]

If your amount values could be the same between companies, you might need to create a combined key in the script for Company and amount to make your selections in.

If you need more help, please post a small sample QVW.

[swuehl]

Strange, I've edited my reply, but it won't show in the thread:

=Aggr(Rank(-Price)=1,Company,Amount)

[edit: replace =1 with <=2 etc. to select not min value, but lowest two values etc.]

[#4]

swuehl, I do not need to use both filters at the same time, just to know how to do it. As for Amount, yes, there are could same amounts of different companies with same/not price. Also, I tried your =Aggr(Rank(-Price)=1,Company,Amount), but it did not work.

[swuehl]

Could you post a small sample QVW?

[#6]

I attached it to the main post.

[swuehl]

Sorry, I've missed that you edited your original post and added a sample.

See attached for a suggested solution using an expression like

=Only(Aggr( If( Rank(-avg(Quantity)) <= vTest, Avg(Quantity)),Company, Amount))

[#8]

Hi Please try this expression

=if(aggr(rank(min(Price)),Company)<=1,Amount)

For second recruitment Try this

if(aggr(rank(min(Price)),Company)<=2,Amount)

[#9]

swuehl‌, I can not open your file because of Personal Edition. I tried your expression, but it did not work because it could not identify vTest.

DeepaMishra‌,  both your expressions show the wrong values as well as use Amount as X-axis instead of Quantity.

[swuehl]

vTest is just a variable, I created a slider object to set the variable to 1,2 or 3.

So the expression should filter the lowest, lowest and second lowest, three lowest prices per company.