Dashboard table - data expression for wide format

[DataOfficer]

I'm wondering if anyone can assist with a data expression, or let me know if what I am aiming for is not possible. 

I have a dataset from wildlife monitoring, which captures the date of a species observation, the lifestage (e.g. adult, juvenile), the sex (male, female, unsexed) and the quantity (integer) observed. Multiple observations can occur on the same date. Each combination of the above fields is on a separate row (i.e. in 'long' format). Example below.

date	lifestage	sex	quantity
01/03/2025	adult	male	2
01/03/2025	adult	male	5
01/03/2025	juvenile		3
02/03/2025	adult	female	8
05/03/2025	metamorph		1
05/03/2025	adult	male	3
12/03/2025	adult		12
14/03/2025	adult	female	6
14/03/2025	adult	female	4

I am trying to use the Dashboards Table element to produce a summary table of this data, where each date is represented as a row, and then subsequent columns show the daily total for each lifestage*sex combination. For example:

date	adult male	adult female	adult unsexed	juvenile	metamorph
01/03/2025	7	0	0	3	0
02/03/2025	0	8	0	0	0
05/03/2025	3	0	0	0	1
12/03/2025	0	0	12	0	0
14/03/82025	0	10	0	0	0

I've gotten so far using Filter and GroupBy statements to summarise counts by day (e.g. resulting in the table format below), but I've not been able to produce a 'wide' format table. Is there any way of doing this?

date	type	total
01/03/2025	adult male	7
01/03/2025	juvenile	3
02/03/2025	adult female	8

I have based my code so far on this example: https://github.com/Esri/arcade-expressions/blob/master/dashboard/dashboard_data/CombineMultipleLayer... but instead of referencing different layers for each variable, I have tried setting up multiple FeatureSets from the same layer, each with a different filter applied (e.g. for each 'type' above). 

I have also produced something similar to my incorrect output above using the List element, but it still doesn't achieve what I am after.

Any help would be hugely appreciated.

[RPGIS]

Hi @DataOfficer,

So the closest recommendation that I can offer is to create a new featureset from a data expression. This would give you something akin to creating a new featureset from existing data and is populated to your output.

Here is an small example on how to construct a separate featureset as a datasource in Dashboards.

var customfields = [
    { 'name' : 'fieldsA' , 'type' : 'esriFieldTypeSmallInteger' },
    { 'name' : 'fieldsB' , 'type' : 'esriFieldTypeDouble' },
    { 'name' : 'fieldsC' , 'type' : 'esriFieldTypeString', Length:7 }
    ]
	
var Values = { 'attributes': {} }
var Geometry = ''
var FS = FeatureSet(Text({
    'fields': customfields,
    'geometryType':Geometry,
    'features' : Values
    }))
return FeatureSet()

[KenBuja]

This can be done with a single GroupBy statement.

GroupBy(
  fs,
  ["date"],
  [
    {
      name: "Adult Male",
      expression:
        "CASE WHEN lifestage = 'adult' and sex = 'male' THEN quantity ELSE 0 END",
      statistic: "Sum"
    },
    {
      name: "Adult Female",
      expression:
        "CASE WHEN lifestage = 'adult' and sex = 'female' THEN quantity ELSE 0 END",
      statistic: "Sum"
    },
    {
      name: "Adult Unsexed",
      expression:
        "CASE WHEN lifestage = 'adult' and sex is null THEN quantity ELSE 0 END",
      statistic: "Sum"
    },
    {
      name: "Juvenile",
      expression:
        "CASE WHEN lifestage = 'juvenile' THEN quantity ELSE 0 END",
      statistic: "Sum"
    },
    {
      name: "Metamorph",
      expression:
        "CASE WHEN lifestage = 'metamorph' THEN quantity ELSE 0 END",
      statistic: "Sum"
    }
  ]
);

I replicated your sample data in the Playground and got your table