Calculate statistics based on multiple fields in same layer

[JanetBrewster1]

Hello All,

I'm trying to make a simple algebraic calculation based on multiple fields from the same data layer, specifically, to divide one field by another. A hitch, perhaps, is that I need the input to this calculation to be filterable by multiple selectors from within the Dashboard, i.e. month, year, and/or location, so I don't want to group by any of these options. I need the resulting info to be used in both an indicator and in multiple charts.

I saw something similar on GitHub, https://github.com/Esri/arcade-expressions/blob/master/dashboard/dashboard_data/CalculationAcrossFie... , and mimicked that...but it doesn't work (and I don't quite understand Dictionaries, either, I have to admit). I get "Test execution error: Execution error - Invalid parameter. Verify test data."

Any thoughts would be greatly appreciated. Thank you!

//Divide two fields from the same layer
//Result must be filterable by multiple selectors within the Dashboard
var portal = Portal('https://arcgis.com');

var fs = FeatureSetByPortalItem(
    portal,
    '86ba934a1b4c4ff2ae8188a012256ffc',
    0,
    [
        'TotalSteelhead',
        'TotalTrips'
    ],
    false
);

var ratioDict = {
    'fields': {'name': 'CPUE', 'type': 'esriFieldTypeDouble'},
    'features':
    [{'attributes':
    {'CPUE': Round((SUM(fs,'TotalSteelhead')/SUM(fs,'TotalTrips')),2)}
    }]};

return FeatureSet(ratioDict)

[JenniferAcunto · Accepted solution]

You do not need a data expression for this. You can use an Indicator with a reference field. 

Use the sum of TotalSteelhead as the data source for your indicator.

Add a reference with a type of statistic and use the sum of TotalTrips.

On the Indicator tab, remove the value and reference placeholders.

Click on the curly brackets and pick the {ratio}.

For a full walkthrough follow the first method outlined here, except you don't even need to get into Advanced Formatting

[JanetBrewster1]

Jen -- this is great!! 

I had not seen your Indicator hack before...I would never have figured this out on my own. It worked perfectly for my indicators, and the results adjusted as I used various selectors within the dashboard. YAHOO!!

However, my charts are the other 50% of this problem. Do you have any thoughts on how I could incorporate this calculation for my charts?

Thank you so much for your help!!

Janet

[JenniferAcunto]

I missed that you need this to work in other charts as well. You can only do something like this without using a Data Expression for elements that allow you to aggregate your data and use Advanced Formatting, so really just Indicators and Table elements. All other elements would require a data expression or a complete overhaul of your data. 

I'm a one trick pony with Arcade so data expressions aren't really in my wheelhouse.