Set analysis within set analysis

[#1]

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

[danielrozental]

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

[swuehl · Accepted solution]

Hi

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

[#4]

Thanks very much for the example and the solution Stefan.

[#5]

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