https://community.esri.com/t5/arcgis-dashboards-questions/arcade-data-expression-seperate-data-by-month/m-p/1077720#M4776 <P>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:</P><P>&nbsp;</P><LI-CODE lang="markdown">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</LI-CODE><P>&nbsp;</P><P>&nbsp;</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="jztu6tr.PNG" style="width: 733px;"><img src="https://community.esri.com/t5/image/serverpage/image-id/18209iC12B499513A12440/image-size/large?v=v2&amp;px=999" role="button" title="jztu6tr.PNG" alt="jztu6tr.PNG" /></span></P> Mon, 12 Jul 2021 09:06:33 GMT Merlin 2021-07-12T09:06:33Z https://community.esri.com/t5/arcgis-dashboards-questions/arcade-data-expression-seperate-data-by-month/m-p/1075833#M4752 <P>Hi there,</P><P>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.</P><P>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?</P><P><STRONG>*** the data ***</STRONG></P><P>is a featureset called by <EM>FeatureSetByPortalItem()</EM> with two fields ['<EM>CreationDate</EM>', '<EM>Name</EM>'],</P><P>the return on this looks like:</P><TABLE border="1" width="99.95748299319727%"><TBODY><TR><TD width="33.29081632653062%" height="30px"><STRONG>objectid</STRONG></TD><TD width="33.333333333333336%" height="30px"><STRONG>Name</STRONG></TD><TD width="33.333333333333336%" height="30px"><STRONG>CreationDate</STRONG></TD></TR><TR><TD width="33.29081632653062%" height="24px">41</TD><TD width="33.333333333333336%" height="24px">Me</TD><TD width="33.333333333333336%" height="24px">03 May 2021 10:17:42 am</TD></TR><TR><TD width="33.29081632653062%" height="24px">42</TD><TD width="33.333333333333336%" height="24px">Me</TD><TD width="33.333333333333336%" height="24px">03 Jun 2021 10:21:56 am</TD></TR><TR><TD height="24px">43</TD><TD height="24px">Fa</TD><TD height="24px">03 May 2021 10:28:56 am</TD></TR><TR><TD height="24px">44</TD><TD height="24px">Fa</TD><TD height="24px">03 Jun 2021 09:17:56 am</TD></TR><TR><TD height="24px">45</TD><TD height="24px">Fa</TD><TD height="24px">03 May 2021 10:54:56 am</TD></TR><TR><TD>46</TD><TD>Ki</TD><TD>03 Jun 2021 08:17:56 am</TD></TR><TR><TD height="24px">...</TD><TD height="24px">...</TD><TD height="24px">...</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>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.</P><P><STRONG>*** the desired result ***</STRONG></P><P>is a featureset which is returned by the expression in the following fashion:</P><TABLE border="1" width="100%"><TBODY><TR><TD width="25%"><STRONG>name</STRONG></TD><TD width="25%"><STRONG>two months ago</STRONG></TD><TD width="25%"><STRONG>one month ago</STRONG></TD><TD width="25%"><STRONG>current month</STRONG></TD></TR><TR><TD width="25%">Me</TD><TD width="25%">12</TD><TD width="25%">14</TD><TD width="25%">0</TD></TR><TR><TD width="25%">Fa</TD><TD width="25%">10</TD><TD width="25%">10</TD><TD width="25%">0</TD></TR><TR><TD width="25%">Ki</TD><TD width="25%">14</TD><TD width="25%">12</TD><TD width="25%">0</TD></TR><TR><TD>...</TD><TD>...</TD><TD>...</TD><TD>...</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>it is important to avoid static months (e.g. May instead of "two months ago"), as the featureset shall always display the latest data.</P><P>The values in the colums <EM>two months ago</EM>, <EM>one month ago</EM> &amp; <EM>current </EM>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.</P><P><STRONG>*** the code so far ***</STRONG></P><P>Now to the most important part. Here my commented code:</P><P>&nbsp;</P><P>&nbsp;</P><LI-CODE lang="markdown">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</LI-CODE><P>&nbsp;</P><P>&nbsp;</P><P><STRONG>*** further approach ***</STRONG></P><P>the code so far gives me a featureset in the following fashion:</P><TABLE border="1" width="56.249205642555594%"><TBODY><TR><TD width="25%" height="46px">name</TD><TD width="25%" height="46px">month</TD><TD width="25%" height="46px">number of submissions</TD></TR><TR><TD width="25%" height="24px">Me</TD><TD width="25%" height="24px">May</TD><TD width="25%" height="24px">12</TD></TR><TR><TD height="24px">Me</TD><TD height="24px">Jun</TD><TD height="24px">14</TD></TR><TR><TD height="24px">Fa</TD><TD height="24px">May</TD><TD height="24px">10</TD></TR><TR><TD height="24px">...</TD><TD height="24px">...</TD><TD height="24px">...</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>So I could create a second dictionary with the fields I would like to see:</P><P>var dictTwo = {<BR />fields: [<BR />{ name: "FT_Name", type: "esriFieldTypeString" },<BR />{ name: "oneMon", type: "esriFieldTypeString" },<BR />{ name: "twoMon", type: "esriFieldTypeString" },<BR />{ name: "Year", type: "esriFieldTypeString" },<BR />],<BR />geometryType: "",<BR />features: [],<BR />};</P><P>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.</P><P>&nbsp;</P><P>Thank you for reading my lengthy post, and thanks in advance for making any suggestions.</P><P>All the best,<BR />Merlin</P> Tue, 06 Jul 2021 13:50:05 GMT https://community.esri.com/t5/arcgis-dashboards-questions/arcade-data-expression-seperate-data-by-month/m-p/1075833#M4752 Merlin 2021-07-06T13:50:05Z https://community.esri.com/t5/arcgis-dashboards-questions/arcade-data-expression-seperate-data-by-month/m-p/1077720#M4776 <P>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:</P><P>&nbsp;</P><LI-CODE lang="markdown">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</LI-CODE><P>&nbsp;</P><P>&nbsp;</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="jztu6tr.PNG" style="width: 733px;"><img src="https://community.esri.com/t5/image/serverpage/image-id/18209iC12B499513A12440/image-size/large?v=v2&amp;px=999" role="button" title="jztu6tr.PNG" alt="jztu6tr.PNG" /></span></P> Mon, 12 Jul 2021 09:06:33 GMT https://community.esri.com/t5/arcgis-dashboards-questions/arcade-data-expression-seperate-data-by-month/m-p/1077720#M4776 Merlin 2021-07-12T09:06:33Z