Calculating feature count of attribute type using arcade in ArcGIS Online

Question

I have a simple table saved as a feature in ArcGIS Online. The table structure is very simple:

ID,Date,Type,Location,Total,Sum_typ,cumul

In the last three fields I would like to calculate statistics using arcade:

FID,Date,Type,Location,Total,Sum_typ,sum_loc
0,19.03.2020,confirmed,Hamburg,8,5,3
2,19.03.2020,confirmed,Hannover,8,5,3
3,21.03.2020,not confirmed,Hamburg,8,3,3
4,21.03.2020,not confirmed,Hannover,8,3,3
5,21.03.2020,confirmed,Bremen,8,5,2
6,24.03.2020,not confirmed,Hannover,8,3,3
7,25.03.2020,confirmed,Bremen,8,5,2
8,25.03.2020,confirmed,Hamburg,8,5,3

Total= would be the total number of records in the table which the following arcade calculation would work.

 var tot = sum($layer)

Here I need help

Sum_typ = should contain the total number or records grouped by type

-any record which is confirmed should have a 5 as this is the total amount of "confirmed" cases in the data.

-any record which is not confirmed should have a 3 as this is the total amount of "not confirmed" cases in the data.

sum_loc = total number of records grouped by location The same as above bur groupd according to location:

therefore any data from Hamburg = 3
therefore any data from Hannover = 3
therefore any data from Bremen =2

I can't do this in any other way as far as I can see apart from with arcade. I need to keep the data dynamic so using a summarize function does not work as this creates a static view which is not updated when new records are added.

Answer 1

To count the overall number of records using Arcade:

count($layer)

The following Arcade expression will count the features in the layer with the same "Type" value (of confirmed/not confirmed cases):

Count(
    Filter($layer, "Type = '" + $feature.Type + "'")
);

And similarly for "Location" value:

Count(
    Filter($layer, "Location = '" + $feature.Location + "'")
);

Useful documentation includes https://developers.arcgis.com/arcade/function-reference/data_functions/#filter and https://github.com/Esri/arcade-expressions.