3

I am looking for a way to - well, kind of use a custom table in the print composer like an excel sheet. I've used a custom table in my Print Layout to calculate values based on Atlas features. So far, so good. But now I want to do calculations in the table by accessing fields directly. In this case: Calculating B7 by adding B2 + B4. There seems to be no way as far as I tried in the expression dialogue.

Anybody knows a trick?

enter image description here

1
  • I'm calculating individually rounded aggregates of the visible features of the atlas page in the table. That's why I want to use the table fields rather than the attribute values to calculate my result in field B7. Fields are int. Does that answer your question?
    – Ben
    Dec 16, 2021 at 18:01

1 Answer 1

5

I guess it bears repeating that expecting GIS data structures to behave like spreadsheets - especially in terms of a 'totals' row - is usually going to result in disappointment. More so with the Attribute Table layout item which is just a derivative of source data - I don't think QGIS stores the location of each row.

You could use a convoluted expression - or Python, or generate a virtual layer maybe - to:

  1. generate unique aggregates based on map layout visibility
  2. sort the results in the specific order required and extract the 2nd and 4th rows from #1 - OR reference the specific rows by an identifying value (e.g. "item type" = 'point')
  3. extract the required aggregate values from #2
  4. Sum the results from #3

...but you would still need the "totals" row to be a separate table or text box rather than within the attribute table.


I am not sure how you generated an attribute table that has both sums and counts as separate rows rather than columns.

But to prove that the concept is technically possible, here is an example where I have counted the heights of trees visible in an atlas feature grouped by genus, ordered by genus.

In a separate table I calculated the combined heights of relevant trees; the first column just had static text (e.g. 'Sum of blah') and the second text had an expression. I also filtered this table to show only 1 row, otherwise it would iterate the same value over every feature in the source layer.

For this example I created two such tables, (a) the 2nd and 4th rows (much more convoluted), and (b) the total number of trees that are genus Acacia or Eucalyptus.

enter image description here

But I don't think trying to sum 'the second and fourth rows' is ever a good approach - that's just not good data structuring, if you can instead reference particular values that would be more robust.

Here is the code for table (a)

with_variable('results',
            array_distinct(
                  array_agg(sum("height_m",
                                group_by:="genus",
                                filter:=intersects($geometry,@atlas_geometry)),
                  filter:=intersects($geometry,@atlas_geometry),
                  order_by:="genus")),
            @results[1]+@results[3])

And the code for table (b)

array_sum(
          array_distinct(
                         array_agg(sum("height_m",
                                       group_by:="genus",
                                       filter:=intersects($geometry,@atlas_geometry)),
                         filter:=intersects($geometry,@atlas_geometry) 
                         and "genus" in ('Acacia','Eucalyptus'))))

Replace "genus" with the attribute you want to group by,"height_m" for the attribute you want to aggregate, and 'Acacia' and 'Eucalyptus' with any specific values you want to reference for table (b)

3
  • 2
    A very nice and profound answer with (very important) conceptual considerations. I like this answer - and most of your answers - very much!
    – Babel
    Dec 18, 2021 at 9:42
  • Thank you for taking the time to answer so conceptually. Very much appreciated!
    – Ben
    Jan 21, 2022 at 13:56
  • hi she_weeds, I'm interested in how to generate unique aggregates based on map layout visibility, the result table you are showing and how to get it in my print composition. so far i have a workaround with creating a virtual layer suming up and grouping the values but I'd like to reduce the steps for the map creation. Mar 21 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.