Serial column chart group by week

[MK15]

I am looking for a way to group date data in a serial column chart by week. Currently the options of day and month don’t work for my business needs but I see no option to group by week. Basically, I would like to show the week over week sum of collected data.

I am hoping that the answer does not involve grouping by data expressions because I need to be able to filter the data with a selector and grouped data does not play well with filtering by the selectors.

Any insight will be greatly appreciated. 

@jcarlson @KenBuja @DerekLaw @JenniferAcunto @JohannesLindner 

[JenniferAcunto · Accepted solution]

You will need to use a data expression, but you don't need the data expression to aggregate your data as the serial chart does that for you. You just need the data expression to add a new column to your data that calculates the Week Of date. This ensures that all features with a date in that week fall into the same bucket. As long as you are bringing in the additional fields that you need, the dashboard interactions will still work.

I've had the best results when returning the week of date as a string formatted as YYMMDD. This ensures the sorting makes sense for dates and the chart treats it as a string so it doesn't add extra gaps for days between. 

In the chart settings you can parse out the dates to make the labels easier to read.

// Write an expression that returns a FeatureSet.
// Documentation: https://arcg.is/3c419TD
// Samples: https://arcg.is/HPbi00

// Add portal/layer info
var fs = FeatureSetByPortalItem(
    Portal('[PORTAL URL]'),
    '[ITEM ID]',
    [LAYER NUMBER],
    ['mag', 'eventType', 'sig', 'eventTime'], // Date field for calculation and fields needed for filtering
    false
);

// Create columns for each of your fields
var newDict = {
    fields: [
      { name: "Magnitude", type: "esriFieldTypeDouble" },
        { name: "Event_Type", type: "esriFieldTypeString" },
        { name: "Significance", type: "esriFieldTypeInteger" },
        { name: "Event_Time", type: "esriFieldTypeDate" },
        { name: "Week_String", type: "esriFieldTypeString" }  
        ],
    'geometryType': '',
    "spatialReference": {},
    'features': []
};

// loop through each feature in the layer and populate columns
var i = 0;
for (var f in fs) {
  var edate = f['eventTime']
  var ws = Text(DateAdd(edate, -Weekday(edate), 'days'), "YMMDD")

  newDict.features[i++]={
    attributes:{
        Magnitude: f['mag'],
        Event_Type: f['eventType'],
        Significance: f['sig'],
        Event_Time: f['eventTime'],
        Week_String: ws
    }
   }
}

return FeatureSet(newDict)

[MK15]

@JenniferAcunto thank you so much for the detailed explanation and the code. I added the week string as you stipulated and used it as the category field and it definitely looked much better than before. I did notice though, that even though I set the week of string column in the YYMMDD format as you suggested, I was still seeing gaps in the chart (probably due to the fact that those weeks were missing data in my source data). In the end, I just used the week_string category without parsing the dates and that just shows the sum of collected data for each week_string and looks perfect. Thanks again for your help.

PS: Unfortunately I cannot upload images since my account is new otherwise I would have shown what I am talking about.

[MK15]

@JenniferAcunto  Oh I just realised that the challenge with not parsing the dates is that we don't get to see the range of the week for which the total sum of collected data is referencing so it can be confusing to the users, but on the other hand, the gaps in the data on the chart are also quite ghastly...

[MK15]

@JenniferAcunto  the gaps that I see when I parse the dates look exactly like the gaps that you have in the second image you have where you hadn't formatted the week data in the YYMMDD format which is weird because my data is formatted in the YYMMDD format.