Avoiding round error for sum of percentages in QGIS

Question

I have a number of values that are a proportion of a total and I am expressing them as a percentage (in the print composer legend):

round(aggregate(layer:='Dwelling_Other',aggregate:='count', expression:="Cons_Yr", filter:=("Locality" IN ('Archies Creek') AND "Cons_Yr" >= 1801 AND "Cons_Yr" <= 1900))/aggregate(layer:='Dwelling_Other',aggregate:='count', expression:="Cons_Yr", filter:=("Locality" IN ('Archies Creek') AND "Cons_Yr" >= 1801))*100)

but the totals add up to over 100% (in this case 102). Is it possible to use the round expression in a way that the percentages will be rounded to integers but still sum correctly to 100%? (along the lines of paxdiablo's explanation here https://stackoverflow.com/questions/13483430/how-to-make-rounded-percentages-add-up-to-100)

Answer 1

The most sensible way is to let it be. People who have done any numerical work will recognize that rounding sometimes leads to this situation. If you feel it may provoke concern, I would add a small note somewhere in your output saying "Percentages may not sum to 100% due to rounding".

If this is not acceptable for your use case, you will need to add an additional layer of computation. This is because there is no way each round(aggregate(...)) can be aware of the other ones in parallel and therefore know if it needs to be adjusted, regardless of algorithm.

Since you mention print composer, I'm assuming this is in a layout, probably controlled by an atlas (otherwise you'd just fix it manually). So if you absolutely must do this, the "proper" way would be to add a virtual field to the atlas that uses aggregate to precalculate the absolute numbers for each category as a list (or map). Then create a custom function that implements (in python) one of the algorithms for "sum to 100% rounding" that you've identified already, taking the list and returning it's "modified rounded nth element" to optimally allocate the rounding error, and then invoke that function in your legend. A lot of work, enough that (if you care enough) a manual correction may be more efficient instead. If you really must "fix" this,

Answer 2

You might need a variable in parallel that keeps the record of individual elements in array. So, to display sum you can use that variable. If you're rounding the digits, by manual checking sum would always be greater than 100,in case you've ceil values.