Nested aggregate functions in print layout

Question

I have 3 layers on map, State, County, City. I'm trying to create an Atlas based print layout by the State layer, that aggregates population totals in City layer, grouped by the county.

I can easily extract the data by all the cities in a county

aggregate(
    'City',
    'sum',
    "Population",
    intersects(@geometry, geometry(@parent))
)

Now I'm trying to nest that into the state layer. The best I can come up with is to create an array of the featureid of the counties within the state:

aggregate(
    'County',
    'array_agg',
    "geoid",
    within(@geometry, geometry(@parent))
)

I then take that array and apply an array_foreach to cycle through and run the first aggregate as the expression:

array_foreach(
    aggregate(
        'County',
        'array_agg',
        "geoid",
        within(@geometry, geometry(@parent))
    ),
    aggregate(
        'City',
        'sum',
        "Population",
        intersects(
            @geometry,
            geometry(get_feature('County', 'geoid', @element))
        )
    )
)

This function does return an array, however all values in the array are equal, and are the sum of the first county in the array.

To further test this, I removed the first aggregate function and defined the initial array:

array_foreach(
    array('123','124','125'),
    aggregate(
        'City',
        'array_agg',
        "Population",
        intersects(
            @geometry,
            geometry(get_feature('County', 'geoid', @element))
        )
    )
)

The result was the same. Am I missing something on the for_each command? Is there a better way to do this?

Answer 1

I was able to replicate your issue with a different dataset. Individually, each aggregate expression works, but in this context the @element variable in the array_foreach() scope doesn't seem to be recognised in the aggregate() scope. Possibly there is some undocumented use for @element in aggregate() expression definition that conflicts with or overrides the array_foreach() definition.

It sounds similar to this issue QGIS Calculating sum with filter based on a column from the same layer, using field calculator where aggregate expression scope doesn't always play nice with other expressions.

Except in this instance, you are using values from an array, and whether they're dynamically generated or defined as a static array, the values just don't get fed into aggregate().

---

For now, I would suggest the easiest workaround is to actually calculate the population per County as a separate field in your datasource (even a virtual field, if you can't edit the datasource), and simply refer to that in your atlas layout expression.

I will update this reply if I figure out a way to do it all within an expression.