Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Filter. Min value or/and all except certain values

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

Min or certain value.png

Message was edited by: Vidmat Vidmat

1 Solution

Accepted Solutions
swuehl
MVP
MVP

This is your original bar chart:

bar_start.png

I created a variable vTest and a slider object:

Slider_small.png

Slider_small_props.png

And adding the expression to your chart returned

bar_end.png

View solution in original post

12 Replies
swuehl
MVP
MVP

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

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.]

Not applicable
Author

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

Could you post a small sample QVW?

Not applicable
Author

I attached it to the main post.

swuehl
MVP
MVP

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

Not applicable
Author

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)

Not applicable
Author

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

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.