topic Set analysis within set analysis in QlikView

Set analysis within set analysis

Tue, 18 Oct 2011 20:59:57 GMT

Hi everyone,

I would appreciate some help with the following problem.

For each dimension, CurrentYear, I am trying to select only a single record, per CurrentYear, which has the minimum SalesCalls with the maximum SalesAmount.

I basically need set analysis within set analysis. I also need to solve the problem inside the sheet chart as opposed to within the data load.

There may be multiple rows in each year per the example below:

Header Row	CurrentYear	SalesCalls	SalesAmount
A	2011	5	1000
B	2011	5	850
C	2011	6	2000
D	2011	5	350
E	2010	3	1200
F	2010	3	1500
G	2010	3	1500

The results should show row A for 2011 and either row F or G (I just need it to be a single record).

Thanks in advance,

Craig

Set analysis within set analysis

danielrozental — Tue, 18 Oct 2011 22:50:48 GMT

You can't do this with set analysis, you need to look into the AGGR function.

Re: Set analysis within set analysis

swuehl — Tue, 18 Oct 2011 22:57:40 GMT

maybe like attached?

The expression uses advanced aggregation and looks a bit ugly. Maybe you can simplify it, I don't get any further tonight.

(well, one aggregation and Minstring is due to the fact that you want a single record returned per CurrentYear).

Hope this helps,

Stefan

Set analysis within set analysis

Wed, 19 Oct 2011 12:22:34 GMT

Thanks very much for the example and the solution Stefan.

Set analysis within set analysis

Wed, 19 Oct 2011 12:23:14 GMT

Thanks Daniel and I see from Stefan's solution how the AGGR function can be used to solve the problem.