Arcade Dashborad - Add new values into hosted table

[explocarto]

For the purposes of a dashboard tool, I need to have a set of informations in a single table. To do this I try with Arcade queries to get my result.

Here is my table as a starter. (attachment 1.PNG)

One set of values per year and per type.

 

and this is what I'm looking for as a result (attachement2.PNG)

New fields with values already present in the table

 

I have tried a lot of things using dictionaries but without any convincing results.

Can someone help me?

I have tried a lot of things using dictionaries but without any convincing results.

Can someone help me?

[jcarlson · Accepted solution]

You can't really have a true "table" in a Dashboard. You can have a table-like List element, but you'd need to calculate the new fields using a data expression, which I think is what you're attempting here, yes?

Can you share the code you've used so far and any errors you've gotten? That can be helpful.

Assuming you want a data expression, maybe something like this would work. It's hard to say sight unseen, and you'll probably need to tweak this to match your data, but it's a starting point at least.

var outDict = { 
    'fields': [{'name':'Year', 'type':'esriFieldTypeInteger'},
               {'name':'Type', 'type':'esriFieldTypeString'},
               {'name':'ValueA', 'type':'esriFieldTypeInteger'},
               {'name':'ValueB', 'type':'esriFieldTypeInteger'},
               {'name':'YearPrev', 'type':'esriFieldTypeInteger'},], 
    'geometryType': '', 
    'features': []
}

var f_index = 0

var fs = FeatureSetBy // You can fill this in with however you're accessing your layer

for(var f in fs){
    var t = f.Type
    var y = f.Year - 1
    var prev = Filter(fs, 'Type = @t and Year = @y')
    var prev_a = ''
    var prev_b = ''

    // make sure your filter returned something, then replace prev values
    if(Count(prev) > 0){
        prev_a = First(prev)['ValueA']
        prev_b = First(prev)['ValueB']
    }

    outDict.features[f_index] = {
        'attributes': {
            'Year': f['Year'],
            'Type': t,
            'ValueA': f['ValueA'],
            'ValueB': f['ValueB'],
            'PrevYear': y,
            'PrevValueA': prev_a,
            'PrevValueB': prev_b
         }
     }
     
     f_index++
}

[explocarto]

Hello Josh,

This is exactly what I need. I learned a lot from your code.

Thanks a lot !

Have a nice day