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

Filter. Min value or/and all except certain values

Wed, 25 Nov 2020 16:16:04 GMT

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

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

swuehl — Sun, 06 Mar 2016 12:08:21 GMT

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.

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

swuehl — Sun, 06 Mar 2016 12:12:15 GMT

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

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

Sun, 06 Mar 2016 15:44:03 GMT

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.

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

swuehl — Sun, 06 Mar 2016 16:22:19 GMT

Could you post a small sample QVW?

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

Sun, 06 Mar 2016 16:31:13 GMT

I attached it to the main post.

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

swuehl — Sun, 06 Mar 2016 22:27:04 GMT

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

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

Mon, 07 Mar 2016 06:47:43 GMT

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)

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

Mon, 07 Mar 2016 18:29:13 GMT

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.

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

swuehl — Mon, 07 Mar 2016 22:31:58 GMT

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.

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

Tue, 08 Mar 2016 06:36:54 GMT

swuehl , could you explain to me how to create a slider object to set a variable to 1, 2, 3, please?

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

swuehl — Tue, 08 Mar 2016 10:49:05 GMT

This is your original bar chart:

I created a variable vTest and a slider object:

And adding the expression to your chart returned

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

Tue, 08 Mar 2016 13:30:08 GMT

swuehl‌, thanks. It works.