Sum() returning Nan Value

1332
13
Jump to solution
04-20-2022 09:14 AM
godofsix
New Contributor II

I'm trying to compute the percentage of a field using the sum of all the fields: 

 

$feature.FullVaccin / Sum($feature.FullVaccin)

 

But every time I test, I get 1.... What am I doing wrong?

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
KimGarbade
Occasional Contributor III

Hmmm... odd.  This code works in AGOL, and if I edit the Layer through a feature service in Pro (this is different test data, but the same code as I originally sent structure wise)

 

var myid = $feature.Work_Unit_No
var mysum = 0
var myfs = filter(featuresetbyname($datastore,"TestPoints",['TestInt'],false),'Work_Unit_No = @myid')
if(!IsEmpty(myfs)){
  var mysum = Round(($feature.TestInt/Sum(myfs,'TestInt'))*100,2)
}
return mysum

 

The reason your original code returned 1 every time was because it divided any number it found by itself:

$feature.FullVaccin / Sum($feature.FullVaccin)

So a tweak to the original code would be to create a feature set (in my example below called myfs) and pass that into the Sum function.  If I want the myfs feature set to return all the rows in the Layer's table I can just remove the filter from my original response.

KimGarbade_0-1650485008866.png

var myid = $feature.COJ_FacilityID
var mysum = 0
var myfs = featuresetbyname($datastore,"BogusPumps",['Integer1'],false)
if(!IsEmpty(myfs)){
  var mysum = ($feature.Integer1/Sum(myfs,'Integer1'))
}
return mysum

 If this doesn't work, I'm afraid I'm missing something that is right in front of me, but I just can't put my finger on.

View solution in original post

0 Kudos
13 Replies
KimGarbade
Occasional Contributor III

Try something like this... I'm running this in field calculator and filtering on an ID, but edit at will.

 

var myid = $feature.COJ_FacilityID
var mysum = 0
var myfs = filter(featuresetbyname($datastore,"BogusPumps",['Integer1'],false),"COJ_FacilityID = @myid")
if(!IsEmpty(myfs)){
  var mysum = ($feature.Integer1/Sum(myfs,'Integer1'))
}
return mysum

I ran this on the field "Float1" against this data:

KimGarbade_0-1650476697604.png

 

 

0 Kudos
godofsix
New Contributor II

Thanks so much. I tried it and I still get the same 1 as the output result

0 Kudos
KimGarbade
Occasional Contributor III

Hmmm... what data type is FullVaccin?  Also, are you using Field Calculator?  If so, what is the data type of the field you are calculating?

0 Kudos
godofsix
New Contributor II

Long

0 Kudos
KimGarbade
Occasional Contributor III

If you are calc'ing into a Long also, it might not like getting numbers to the right of a decimal.  Try calc'ing into Float or a Double.

Since percentage is going to be a decimal number by definition it only show 0 or 1 when you calc into an integer type.  

KimGarbade_0-1650478669279.png

if you multiple by 100 it will show an int.

 

0 Kudos
godofsix
New Contributor II

Did and still got 1

godofsix_0-1650478345269.png

Though, my ultimate aim is to use it for custom label in a web map

0 Kudos
KimGarbade
Occasional Contributor III

Still getting 1?  That is odd... are their any nulls in your table that you have to account for or does every row contain a FulVaccin value?

0 Kudos
godofsix
New Contributor II

There are no nulls

Here is the table:

godofsix_0-1650478909883.png

 

0 Kudos
KimGarbade
Occasional Contributor III

Is "FullVaccin" off the page to the left? 

Is the table joined with another table? 

If you run a calc on Percentage again, and this time just  do something simple like

($feature.Calculation * 1000)/Join_Count

(just using data I see in your table); it will return a useless number, but does that result also show 1 every time?

0 Kudos