Multi step filter

ruanhaese — Wed, 25 Nov 2020 16:16:04 GMT

Sitting with a slightly more complex problem and would really appreciate help on this.

I did the exercise in Excel first and realised its a couple of steps to get to a solution

and so Im not sure how to do this in Qlikview (still learning a lot!)

Scenario:

You have projects, vendors and amounts.

Projects can have multiple vendors, Vendors can have multiple projects.

The Steps:

(1) Rank all the vendors by Amount

(2) Take the Top 2 vendor names

(3) Get the list of all the projects attached to those vendors

(4) Get the Top 3 from this list

Ive attached data, on the right hand side of the data you can see the steps.

(1) Ranks the data and Gets V2 and V4 as the Top 2

(2) Gets projects P4, P5, P6 from V2 and V4 gets P5, P7, P8

(3) The new list is then P4, P5, P6, P7, P8

(4) Ranked the final list is P4, P5, P6

Any help with this would be appreciated.

Re: Multi step filter

kaushiknsolanki — Tue, 08 Mar 2016 11:21:54 GMT

Hi,

Have a look at the attached solution.

Regards,

Kaushik Solanki

Gysbert_Wassenaar — Tue, 08 Mar 2016 12:03:24 GMT

Try sum({<Project={"=rank(sum({<Vendor={`=rank(sum(Amount))<=3`}>}Amount))<=2"}>}Amount)

ruanhaese — Tue, 08 Mar 2016 12:53:44 GMT

Wow!

Thanks guys, very helpful as always.