Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
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

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Filter. Min value or/and all except certain values

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

Re: Filter. Min value or/and all except certain values

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.

MVP
MVP

Re: Filter. Min value or/and all except certain values

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

Re: Filter. Min value or/and all except certain values

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.

MVP
MVP

Re: Filter. Min value or/and all except certain values

Could you post a small sample QVW?

Not applicable

Re: Filter. Min value or/and all except certain values

I attached it to the main post.

MVP
MVP

Re: Filter. Min value or/and all except certain values

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

Re: Filter. Min value or/and all except certain values

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

Re: Filter. Min value or/and all except certain values

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.

MVP
MVP

Re: Filter. Min value or/and all except certain values

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.