Arcade Data Expression: seperate data by month

[Merlin]

Hi there,

as a newcomer to Arcade data expressions in the dashboards (and in general) I have had an exiting day so far playing around with my data.

My current challenge has brought me now to a point of trickyness I havn't been able to overcome. Maybe someone out there might help me out?

*** the data ***

is a featureset called by FeatureSetByPortalItem() with two fields ['CreationDate', 'Name'],

the return on this looks like:

objectid	Name	CreationDate
41	Me	03 May 2021 10:17:42 am
42	Me	03 Jun 2021 10:21:56 am
43	Fa	03 May 2021 10:28:56 am
44	Fa	03 Jun 2021 09:17:56 am
45	Fa	03 May 2021 10:54:56 am
46	Ki	03 Jun 2021 08:17:56 am
...	...	...

the table continues with several hundred entries of around 10 different names and unique creation dates ranging from beginning of May until end on June.

*** the desired result ***

is a featureset which is returned by the expression in the following fashion:

name	two months ago	one month ago	current month
Me	12	14	0
Fa	10	10	0
Ki	14	12	0
...	...	...	...

it is important to avoid static months (e.g. May instead of "two months ago"), as the featureset shall always display the latest data.

The values in the colums two months ago, one month ago & current are the result of a count calculation. It shall indicate how many entries have been made per Name in the respective months. As there are no submissions in the current month, it shall display a 0.

*** the code so far ***

Now to the most important part. Here my commented code:

var p = 'https://xxx.maps.arcgis.com'
var itemID = 'xxx'
var layerID = 0

var attendance = FeatureSetByPortalItem( Portal(p),itemID,layerID, ['CreationDate', 'FT_Name'], false );

// three variables to determine the current, last and second last month

var currentMonISO = ISOMonth(today());
var oneMonAgoISO = currentMonISO-1;
var twoMonAgoISO = currentMonISO-2;

//empty dictonary to store the desired result

var dict = {
fields: [
{ name: "FT_Name", type: "esriFieldTypeString" },
{ name: "month", type: "esriFieldTypeString" },
{ name: "Year", type: "esriFieldTypeString" },
// { name: "list_custom_order", type: "esriFieldTypeInteger" }
],
geometryType: "",
features: [],
};

// looping through the featureset to populate the dictionary with data and converting the Months into human language

var index = 0;

for (var feature in attendance) {
dict.features[index] = {
'attributes': {
'FT_Name': Text(feature["FT_Name"]),
'month': Decode(ISOMonth(feature["CreationDate"]),
1, "Jan",
2, "Feb",
3, "Mar",
4, "Apr",
5, "May",
6, "Jun",
7, "Jul",
8, "Aug",
9, "Sep",
10, "Oct",
11, "Nov",
12, "Dec",
"missing data"),
'Year': ISOYear(feature["CreationDate"]),
}}
index++;}

var fs_dict = FeatureSet(Text(dict));

// using GroupBy() to display the data grouped by name
var groupName = GroupBy(fs_dict, ["FT_Name","month", "year"], [
{
name: "trainings per month",
expression: "month",
statistic: "COUNT"
}])

return groupName

*** further approach ***

the code so far gives me a featureset in the following fashion:

name	month	number of submissions
Me	May	12
Me	Jun	14
Fa	May	10
...	...	...

So I could create a second dictionary with the fields I would like to see:

var dictTwo = {
fields: [
{ name: "FT_Name", type: "esriFieldTypeString" },
{ name: "oneMon", type: "esriFieldTypeString" },
{ name: "twoMon", type: "esriFieldTypeString" },
{ name: "Year", type: "esriFieldTypeString" },
],
geometryType: "",
features: [],
};

My idea so far was to somehow filter the featureset populating the dictionary. But I didnt manage to modify the first method to populate the dictionary via for() loop in a way that I would get what I was looking for.

Thank you for reading my lengthy post, and thanks in advance for making any suggestions.

All the best,
Merlin

[Merlin · Accepted solution]

Got it solved by the following expression (see result below). If there are any suggestions how to write a more elegant code, I would still appreaciate to learn it:

var p = "https://herewegrow.maps.arcgis.com"
var itemID = "f8602c47a194470b89a75174a9d1153b"
var layerID = 0

var attendance = FeatureSetByPortalItem( Portal(p),itemID,layerID, ["CreationDate", "FT_Name", ""], false );
var currentMonISO = ISOMonth(today());
var oneMonAgoISO = currentMonISO-1;
var twoMonAgoISO = currentMonISO-2;
var currentMon = Decode(currentMonISO,    
            1, "Jan",
            2, "Feb",
            3, "Mar",
            4, "Apr",
            5, "May",
            6, "Jun",
            7, "Jul",
            8, "Aug",
            9, "Sep",
            10, "Oct",
            11, "Nov",
            12, "Dec",
            "missing data");
var oneMonAgo = Decode(oneMonAgoISO,    
            1, "Jan",
            2, "Feb",
            3, "Mar",
            4, "Apr",
            5, "May",
            6, "Jun",
            7, "Jul",
            8, "Aug",
            9, "Sep",
            10, "Oct",
            11, "Nov",
            12, "Dec",
            "missing data");
var twoMonAgo = Decode(twoMonAgoISO,    
            1, "Jan",
            2, "Feb",
            3, "Mar",
            4, "Apr",
            5, "May",
            6, "Jun",
            7, "Jul",
            8, "Aug",
            9, "Sep",
            10, "Oct",
            11, "Nov",
            12, "Dec",
            "missing data");

var dict = {
  fields: [
    { name: "FT_Name", type: "esriFieldTypeString" },
    { name: "month", type: "esriFieldTypeString" },
    { name: "Year", type: "esriFieldTypeString" },
  ],
  geometryType: "",
  features: [],
};
var index = 0; 
for (var feature in attendance) { 
    dict.features[index] = { 
        "attributes": { 
            "FT_Name": Text(feature["FT_Name"]), 
            "month": Decode(ISOMonth(feature["CreationDate"]),    
            1, "Jan",
            2, "Feb",
            3, "Mar",
            4, "Apr",
            5, "May",
            6, "Jun",
            7, "Jul",
            8, "Aug",
            9, "Sep",
            10, "Oct",
            11, "Nov",
            12, "Dec",
            "missing data"),
            "Year": ISOYear(feature["CreationDate"]),
        }} 
    index++;} 
var fs_dict = FeatureSet(Text(dict));

var currentMon = filter(fs_dict, "month = @currentMon");
var oneMonAtt = filter(fs_dict, "month = @oneMonAgo");
var twoMonAtt = filter(fs_dict, "month = @twoMonAgo");

var cDict = {
  fields: [
    { name: "FT_Name", type: "esriFieldTypeString" },
    { name: "currentMon", type: "esriFieldTypeString" },
    { name: "oneMonAgo", type: "esriFieldTypeString" },
    { name: "twoMonAgo", type: "esriFieldTypeString" },
    { name: "Year", type: "esriFieldTypeString" },
  ],
  geometryType: "",
  features: [],
};

var i = 0;
for (var c in currentMon) {
    cDict.features[i++] = {
        attributes: {
            FT_Name: c["FT_Name"],
            oneMonAgo: c["month"],
            Year: c["year"],
        },
    };
}

for (var o in oneMonAtt) {
    cDict.features[i++] = {
        attributes: {
            FT_Name: o["FT_Name"],
            oneMonAgo: o["month"],
            Year: o["year"],
        },
    };
}

for (var t in twoMonAtt) {
    cDict.features[i++] = {
        attributes: {
            FT_Name: t["FT_Name"],
            twoMonAgo: t["month"],
            Year: t["year"],
        },
    };
}
var fs_cDict = FeatureSet(Text(cDict));

var result = GroupBy(fs_cDict, ["FT_Name", "Year"],
[{
    name: "current Month",
    expression: "currentMon",
    statistic: "COUNT"
},{
    name: "one month ago",
    expression: "oneMonAgo",
    statistic: "COUNT"
},
{
    name: "two months ago",
    expression: "twoMonAgo",
    statistic: "COUNT"
}]);

return result