cancel
Showing results for
Did you mean:
Partner - Contributor II

## Set analysis - Using aggregate and set analysis

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

1 Solution

Accepted Solutions
MVP

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

4 Replies
MVP

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

Partner - Contributor II
Author

Thank you Manish Kachhia. You've solved it.

Oscar

MVP