Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

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:

cars - database.png

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:

cars - return set.png

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
MVP

Re: Set analysis - Using aggregate and set analysis

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

View solution in original post

4 Replies
MVP
MVP

Re: Set analysis - Using aggregate and set analysis

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

View solution in original post

Partner
Partner

Re: Set analysis - Using aggregate and set analysis

Thank you Manish Kachhia. You've solved it.

Oscar

MVP
MVP

Re: Set analysis - Using aggregate and set analysis

Don't you get your answer yet?

Let me know what is missing?

Partner
Partner

Re: Set analysis - Using aggregate and set analysis

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