2

I need to create an attribute with cumulative sum of another attribute. The values are sorted by a string attribute. I just found posts that involved the numerical id of the features.

I've tried to make a expression with the previous feature name but it only works for the first three features in the table, all after that remain NULL.

That's the expression:

if (name = 'P00-P01', attribute('dist'), attribute('dist') + attribute(get_feature('route', 'name', if (to_int(substr(name, 2,2))-1<10, concat( 'P0', to_string(to_int(substr(name, 2,2))-1),'-',substr(name ,0,3 )), concat( 'P', to_string(to_int(substr(name, 2,2))-1),'-',substr(name ,0,3 )))),'cum_dist'))

Table with only three cumulative values

Using the index in the expression the result remains the same.

if (name = 'P00-P01', attribute($currentfeature, 'dist'), attribute($currentfeature, 'dist') + attribute(get_feature_by_id('route', $id-1), 'cum_dist'))

I've tried also the aggregated() function but every time the QGIS crashes.

Someone have a guess about this bug?

4
  • Is your cum_dist field a virtual field? If not, try if inserting the variables refering to cum_dist inside the current_value() function docs.qgis.org/3.22/en/docs/user_manual/expressions/…
    – ThiPa
    Apr 2, 2022 at 5:48
  • Both fields are virtual fields. The field dist have an expression $lenght of sequential lines to provide these values. I've tried to create another virtual field with the sum of other attribute, also virtual and integer. The result was the same bug, only the three next features were updated. Apr 2, 2022 at 12:26
  • Maybe thid works (can't test, afk for the weekend) : aggregate(layer:='layer name here', aggregate:='sum', expression:=$length, filter:="id"<=$id)
    – ThiPa
    Apr 2, 2022 at 20:37
  • It fills the features with the same value: the sum of all features length. It looks like the filter is being ignored by the calculator. PS.: my "id" field is "fid" because I'm using a gpkg file. Apr 3, 2022 at 14:18

2 Answers 2

2

Solution using PyQgis (python)

I could not figure out how to solve this using expressions (but saw a solution after posting, see below), so I'll suggest you to use python for the solution. The following code should do the job:

# lines beginning with """ are comments to describe a block of code
# lines and text starting with # are comments too

layer = QgsProject.instance().mapLayersByName('route') # returns a list of all layers called "route" in your interface
layer = layer[0] # returns the first layer of the list

"""add a new field called 'cum_dist'"""
pr = layer.dataProvider() # set the data provider
pr.addAttributes([QgsField('cum_dist', QVariant.Double)]) # adds a field to your layer
layer.updateFields() # update the layer fields

"""add values to the new cum_dist field"""
cum_dist = 0 # initialize the distance at 0
for feature in layer.getFeatures(): # loop over all features with a for loop
    geom = feature.geometry() # get the geometry of the feature
    length = geom.length() # get the length of the geometry
    cum_dist = cum_dist + length # update the cumulative distance
    
    with edit(layer): # this sets the layer in edit mode for this code block
        feature['cum_dist'] = cum_dist # set the value of the field 'cum_dist' to the value of the variable cum_dist
        layer.updateFeature(feature) # update the feature values

Sources:
https://anitagraser.com/pyqgis-101-introduction-to-qgis-python-programming-for-non-programmers/pyqgis101-creating-editing-a-new-vector-layer/
https://gis.stackexchange.com/a/168872/200573

Solution using expression

with_variable (
    'fid',
    fid,
    sum( $length, filter:=fid<=@fid)
)

Source: Cumulative SUM in QGIS attribute table

2
  • 2
    OP wants sorted by string attribute, not by fid
    – Babel
    Apr 4, 2022 at 12:37
  • In QGIS 3.10, version available for my Raspberry Pi, the expression solution result in fill all the features with the same value, the total value. Oddly, in QGIS 3.18 it works perfectly, using both fid (integer) or name (string) to build the filter. In the RPi I implemented the PyQGIS solution that worked fine but generated regular fields, not virtual fields. It solved the issue but the virtual field was more efficient because was necessary run the python code manually every time that dist field was modified, updating all the fields for each base modification. Apr 6, 2022 at 22:45
2

Use this expression and replace value and name by the field names you have:

array_sum (
    array_slice (  -- return a portion of the array, starting with... (see below)
        array_agg (value, order_by:=name),  -- create an array of all values, ordered alphabetically based on field name
        0,   -- ... starting with the 1st one (index 0) ...
        array_find (
            array_agg (name, order_by:=name), 
            name  -- ...and going up until the nth value, where n is the position of the current string in the alphabetic list of all strings
        )
    )
)

enter image description here

2
  • I am using QGIS 3.10, the latest available for Raspberry Pi architecture, and this function doesn't exist in this QGIS version. Although when I tested in QGIS 3.18 it worked perfectly, using fid or name fields, it fits in both numerical or strings fields to get the correct order. Apr 6, 2022 at 22:35
  • If you use older software versions, this information is keyand should be included in the question body.
    – Babel
    Apr 7, 2022 at 6:17

Your Answer

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

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