5

I have an attribute table in QGIS in which I would like to calculate the cumulative sum. The table is sorted by N (number of fatalities) and I would like to calculate the cumulative sum on F (frequency of accident), i.e. F_cumulative should be a sum of the current row and the rows above. How should I do this? Can I use a formula in the field calculator? enter image description here

1
  • Welcome to GIS SE. As a new user, please be sure to take the short tour to learn about this site's focused Q&A format.
    – Andy
    Feb 20, 2019 at 15:31

1 Answer 1

10

It can be done with an expression. Bear in mind this only works if the original table is already ordered with N descending as feature IDs increase. Since the result field is empty, we need to get past NULL values ruining the calculation. We'll use a conditional statement to fill the first line with a different value than the rest, so subsequent calculations can be based a numerical value. In the field calculator, write this expression (modifying the relevant layer/field names):

if($id = 1, attribute($currentfeature, 'your_frequency_field'), attribute($currentfeature, 'your_frequency_field') + attribute(get_feature_by_id('your_layer', $id-1), 'your_cumulative_field'))

This will calculate a cumulative sum in order of feature IDs by fetching the frequency field attribute value of the currently evaluated feature and adding the cumulative frequency field attribute value of the previous feature (with the exception of feature ID = 1 which will fetch its own value: 0).

With a quick mockup of your data, this was my result summing on F and F_cum:

enter image description here

4
  • Thanks, but I want to calculate the cumulative sum of the current row and ALL the rows above. In Excel I calculate it as a sum of F on the current row with F_cumulative on the row above. I tried to change your expression to do this in QGIS but all I get is NULL values. Any suggestions on how I can do this?
    – Robert K
    Feb 21, 2019 at 10:41
  • @RobertK I modified the answer to address the issues.
    – Gabriel
    Feb 21, 2019 at 14:45
  • Using the following expression is not needed to first fill with 0 your cumulative field: if($id = 1, attribute($currentfeature, 'your_frequency_field'), attribute($currentfeature, 'your_frequency_field') + attribute(get_feature_by_id('your_layer', $id-1), 'your_cumulative_field')) Oct 11, 2019 at 6:26
  • @AndreaGiudiceandrea That's true, I guess I was lazy when the OP raised the issue. I'll modify the answer.
    – Gabriel
    Oct 11, 2019 at 13:28

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.