Multi step filter

[ruanhaese]

Hi

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.

[kaushiknsolanki · Accepted solution]

Hi,

Have a look at the attached solution.

Regards,

Kaushik Solanki

[Gysbert_Wassenaar]

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

[ruanhaese]

Wow!

Thanks guys, very helpful as always.