Arcade Data Expression: seperate data by month

518
1
Jump to solution
07-06-2021 06:45 AM
Labels (1)
Merlin
by
New Contributor III

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:

objectidNameCreationDate
41Me03 May 2021 10:17:42 am
42Me03 Jun 2021 10:21:56 am
43Fa03 May 2021 10:28:56 am
44Fa03 Jun 2021 09:17:56 am
45Fa03 May 2021 10:54:56 am
46Ki03 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:

nametwo months agoone month agocurrent month
Me12140
Fa10100
Ki14120
............

 

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:

namemonthnumber of submissions
MeMay12
MeJun14
FaMay10
.........

 

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

---
0 Kudos
1 Solution

Accepted Solutions
Merlin
by
New Contributor III

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

 

 

jztu6tr.PNG

---

View solution in original post

1 Reply
Merlin
by
New Contributor III

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

 

 

jztu6tr.PNG

---