
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nested Aggregation error
Hi,
I'm using the solution found here: https://community.qlik.com/t5/App-Development/Aggregating-in-Load-Script/td-p/1239697
Data:
LOAD * INLINE [
Client, Company, # of Employees (Client Level)
1, 1, 10
2, 1, 10
3, 2, 5
4, 2, 5
5, 2, 5
6, 2, 5
7, 2, 5
];
Company_Totals:
LOAD Company,
sum([# of Employees (Client Level)]) AS [# of Employees (Company Level)]
RESIDENT Data
GROUP BY Company;
The script works just fine, using group by to simulate a aggr function. But I need to take this one step further. I need to get the max value of the new table for # of Employees (Company Level) (25 in the example above). My problem is that when I try to add a new column of the maximum of # of Employees (Company Level), I either get a nested aggregation error (since I'm trying to find the max of a sum) or I try to find the max value in the sheet instead of the script but then when I filter by company, the max changes based on my filter selection.
My ultimate goal is to create an equation to output a single number on the sheet based on the filter selection. The equation will be # of employees at selected company/max number of employees between all company + another metric for selected company/max number of metric between all companies + etc etc
It would be best (if possible) to do all the calculations before proceeding to the sheet so that everything is in a single table and properly organized by company.
Accepted Solutions

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Remove the company totals from the script. Aggregations made in the script do not react to user selections.
In the UI, you can make a chart with
Dimension: Company
Measure1: sum([# of Employees (Client Level)])
Measure2: Max(total Aggr(sum([# of Employees (Client Level)]), Company))
And then you divide Measure1 with Measure2.
If you want Measure2 to be insensitive to selections, you should try
Measure2: Max({1} total Aggr(sum({1} [# of Employees (Client Level)]), Company))

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Remove the company totals from the script. Aggregations made in the script do not react to user selections.
In the UI, you can make a chart with
Dimension: Company
Measure1: sum([# of Employees (Client Level)])
Measure2: Max(total Aggr(sum([# of Employees (Client Level)]), Company))
And then you divide Measure1 with Measure2.
If you want Measure2 to be insensitive to selections, you should try
Measure2: Max({1} total Aggr(sum({1} [# of Employees (Client Level)]), Company))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you! That worked like a charm!
