Aggr DATA : Zero when no record for specific field value

[Jeffx]

I have the following data :

Item	Year	Qty
42.1234	2020	10
42.1234	2020	20
42.1234	2020	30
42.1234	2022	40
42.1234	2022	50
42.1234	2022	60

And I would like get this table where all possible field values for Year are listed :
[Possible values for Year field are 2020, 2021, 2022]

Item	Year	Total
42.1234	2020	60
42.1234	2021	0
42.1234	2022	150

Note that there are no records for the Year 2021 in the database, how can I achieve this ?

I tried using the "Aggr" function, but the output does not contain any line for 2021.

Aggr(Sum(Qty), Item, Year)

Item	Year	Total
42.1234	2020	60
42.1234	2022	150

[KGalloway · Accepted solution]

If 2021 does not exist in the data, it will not appear in any aggregations.

You could put in some "dummy records" for missing years (like 2021).

For example:

Item         Year     Total

42.1234   2020    10

42.1234   2021     -

42.1234   2022    40

[KGalloway]

Sorry! Clicked reply before I added the screenshot:

[Jeffx]

Thanks, that's what I thought. Unfortunately I do not have access to the database, how can I add data to an existing table ?

[KGalloway]

If you have access to the load script of the app, you could add it there by loading the table and then concatenating the "dummy records".