Count incidents per year - output CSV QGIS

Question

I have a points dataset of fire incidents, where each incident's date is recorded.

I am currently counting the number of incidents per year, by selecting features by expression - using the expression ACQ_DATE LIKE '%2001%' , ACQ_DATE LIKE '%2002%' etc for each year from 2001-2021.

I wish to do this automatically using one expression, which outputs a CSV that looks like the following.

Answer 1

The easiest way would be to use a virtual layer :

• Go to : > Layer > Add Layer > Add/Edit Virtual Layer...
• In the "Create virtual Layer" dialog click on import and choose your point dataset
• In the query part make an SQL query like this one : SELECT year(ACQ_DATE) as year, COUNT(ACQ_DATE) as 'number of incidents' FROM YOUR_LAYER GROUP BY year(ACQ_DATE); (replace "YOUR_LAYER" by the actual layer name of your point dataset)
• Click the "Add" button

This will add a table with two column : year and number of incident, you could easily export this table as CSV by right clicking on it and choose Export > Save Feature As...

Answer 2

Use this expression. Normalle, if using field calculator, it calculates the same list (each year + number of entries per year) separately for each feature. To avoid that, simply select one feature and then run field calculator with the box checked next to Only update 1 selected features.

If your date is indeed formatted as date (not as text), instead of left (ACQ_DATE,4) you can use year (ACQ_DATE).

replace (
    array_to_string (
        array_sort (
            array_distinct (
                array_agg (
                    left (ACQ_DATE,4)  || ': ' || 
                    array_length(
                        array_agg( 
                            to_int (left (ACQ_DATE,4)), 
                            group_by:=to_int(left (ACQ_DATE,4))
                    ))  || '\n'
    )))),
    ',',
    ''
)