Division operator not working in Arcade Expression

[ScottFortman1]

Hi there community!

I am really hoping for some help on this, it is driving me mad!  I have an Arcade script being used in an indicator widget in a dashboard on10.9.1 Portal and works great.  I am migrating same dashboard and widget to 11.3.1.  I copied the original script to new dashboard, changed the Portal location, service id, layer id, and the fields I pull in are identical.  The script is just doing a Groupby function to create a table with a handful of existing fields plus two new fields that are calculated.  The first one is using a multiply math operator and works fine.  The second expression is using division and I get a "Test execution error: Unknown Error. Verify test data.

I have commented out the second expression and the script works, so I know it is good up to there.  If I change the division operator to addition, subtraction, or multiplication, it also works, just not the value I need.

Any help or thoughts are greatly appreciated, Thanks!

Script where second expression doesn't work:

var tvwdportal = Portal('https://acadia.tvwd.org/portal')
var flush = FeatureSetByPortalItem(tvwdportal,"4159ad26944145339b8ae97ea484c331",70,['Duration', 'EstimatedFlushTime', 'Turbidity','StartTime', 'StopTime','assetid'],true)
 
groupby(
    flush,
    ["StopTime", "assetid", "Turbidity","EstimatedFlushTime", "Duration",
        
    ],
    [
    {name:'EstimatedTimex2', expression: 'EstimatedFlushTime * 2', statistic: 'MAX'},    
    {name: 'PipeVolumeTurnover', expression: 'Duration / EstimatedFlushTime', statistic: 'SUM'} 
        ]
        )

Script and table if I change operator on second expression (Line11)

var tvwdportal = Portal('https://acadia.tvwd.org/portal')
var flush = FeatureSetByPortalItem(tvwdportal,"4159ad26944145339b8ae97ea484c331",70,['Duration', 'EstimatedFlushTime', 'Turbidity','StartTime', 'StopTime','assetid'],true)
 
groupby(
    flush,
    ["StopTime", "assetid", "Turbidity","EstimatedFlushTime", "Duration",
        
    ],
    [
    {name:'EstimatedTimex2', expression: 'EstimatedFlushTime * 2', statistic: 'MAX'},    
    {name: 'PipeVolumeTurnover', expression: 'Duration * EstimatedFlushTime', statistic: 'SUM'} 
        ]
        )

StopTime assetid Turbidity EstimatedFlushTime Duration EstimatedTimex2 PipeVolumeTurnover ROW__ID

null	"FLSH105902"	null	null	null	null	null	149875
null	"FLSH105911"	1	21.90297563	60	43.805951	1314.178538	150675
null	"FLSH105985"	1.54	4.23492962	30	8.469859	127.047889	159472
null	"FLSH105987"	null	null	null	null	null	159872
null	"FLSH106037"	null	null	null	null	null	165475
null	"FLSH106067"	null	null	null	null	null	167875

[CodyPatterson]

Hey @ScottFortman1 

This usually happens if there's a chance that the value could be NULL or 0, which cannot be calculated, use this here instead:

{name: 'PipeVolumeTurnover', expression: 'IIF(!IsEmpty(EstimatedFlushTime) && EstimatedFlushTime > 0, Duration / EstimatedFlushTime, 0)', statistic: 'SUM'}

If it's greater than 0, it should work out for you! Edited for null avoidance too.

Cody

[ScottFortman1]

Thanks for reply, Cody.  I pasted that in and I still got this:

Test execution error: Expected "!=", ")", "*", "+", ",", "-", "/", "<", "<=", "<>", "=", ">", ">=", "AND", "BETWEEN", "IN", "IS", "LIKE", "NOT", "OR", "||", or [ \t\n\r] but "&" found.. Verify test data.

So I actually ran a filter at the feature set definition and that fixed my original expression.  The insight on the null or 0 values was a huge help though.

[CodyPatterson · Accepted solution]

Hey @ScottFortman1 

Great to hear you got it! So strange that || works but && doesn't just how Arcade and JavaScript differ! Here's a corrected one from the above, even though you got it all checked out:

IIF(!IsEmpty(EstimatedFlushTime) AND EstimatedFlushTime > 0, Duration / EstimatedFlushTime, 0)

Cody