topic Set analysis - Using aggregate and set analysis in QlikView

Set analysis - Using aggregate and set analysis

oscarneo — Mon, 23 Jan 2017 09:36:15 GMT

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

Re: Set analysis - Using aggregate and set analysis

MK_QSL — Mon, 23 Jan 2017 10:21:01 GMT

Use this expression

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

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

Re: Set analysis - Using aggregate and set analysis

oscarneo — Mon, 23 Jan 2017 10:38:54 GMT

Thank you Manish Kachhia. You've solved it.

Oscar

Re: Set analysis - Using aggregate and set analysis

MK_QSL — Mon, 23 Jan 2017 10:39:39 GMT

Don't you get your answer yet?

Let me know what is missing?

Re: Set analysis - Using aggregate and set analysis

oscarneo — Mon, 23 Jan 2017 10:42:49 GMT

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