Nested Aggregation error

[amr_qlik]

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.

[hic · Accepted solution]

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

[amr_qlik]

Thank you!  That worked like a charm!