Set analysis - Using aggregate and set analysis

[oscarneo]

Hello all,

I have a list of invoices of cars sold. Each invoice has a car, a car maker, invoice date and invoice value. Basically my table is:

What I need to do is to bring in a table all the cars from the car maker that has at least one (could be more) car that sold more than £16,000.00 for a specific month using Set Analysis.

I'm able to return in a table all the cars that has sold more than £16,000.00, however it was not exhibiting the other cars from the same car makers.

=sum({<Car = {'=Aggr(Sum([Invoice Value]), Car)>16000'}>} [Invoice Value])

The result was:

What I'm looking for is return in addition to Clio, return also Captur and Megane where even though their individual sold was less than $16,000.00 they are part of Renault which has Clio that sold more £16,000.00.

Attached has the Excel file and the Qlikview I've used.

I'll be glad if you send me some suggestions.

Regards,

Oscar

[MK_QSL · Accepted solution]

Use this expression

=SUM({<[Car Brand] = P({<Car = {"=SUM([Invoice Value])>16000"}>}[Car Brand])>}[Invoice Value])

Or

=SUM({<[Car Brand] = P({1<Car = {"=SUM([Invoice Value])>16000"}>}[Car Brand])>}[Invoice Value])

[oscarneo]

Thank you Manish Kachhia. You've solved it.

Oscar

[MK_QSL]

Don't you get your answer yet?

Let me know what is missing?

[oscarneo]

Yes I did. I'm just trying to find out how to mark that as the correct answer.