Add a New Var w/o New Layer
Hi Lindsey Whittington ,
Just to know if I am on the right track have a look at the green text in the image below:
What I do, is using the geoid_1 field to identify the location and then filter the layer using this geoid_1 and loop through the results to show the final values through the years. Are you looking for something like this or do you want to extract statistics of the data?
The expression I used was:
// read out the location ID
var geoid = $feature["geoid_1"];
// create a query with that ID
var sql = "geoid_1 = @geoid";
// filter the data using the query
var fs = OrderBy(Filter($layer, sql), "State_Year ASC");
// get the count, and only process if you have results
var result = "Male % through the years:";
var cnt = Count(fs);
if (cnt > 0) {
// loop through results
for (var f in fs) {
result += TextFormatting.NewLine + " - " + Round(f["Value_Final"], 1) + "% (" + f["State_Year"] + ")";
}
}
return result;
Hi Lindsey Whittington ,
Sure, you can put in a condition that checks for the -1 value:
// read out the location ID
var geoid = $feature["geoid_1"];
// create a query with that ID
var sql = "geoid_1 = @geoid";
// filter the data using the query
var fs = OrderBy(Filter($layer, sql), "State_Year ASC");
// get the count, and only process if you have results
var result = "Male % through the years:";
var cnt = Count(fs);
if (cnt > 0) {
// loop through results
for (var f in fs) {
var finalval = f["Value_Final"];
if (finalval == -1) {
result += TextFormatting.NewLine + " - Data Not Available (" + f["State_Year"] + ")";
} else {
result += TextFormatting.NewLine + " - " + Round(f["Value_Final"], 1) + "% (" + f["State_Year"] + ")";
} }
}
return result;Which will show up like this:
Maybe that is not what you want and you can do it differently by switching off including any results that have no data available (see line 16 which has been commented out):
// read out the location ID
var geoid = $feature["geoid_1"];
// create a query with that ID
var sql = "geoid_1 = @geoid";
// filter the data using the query
var fs = OrderBy(Filter($layer, sql), "State_Year ASC");
// get the count, and only process if you have results
var result = "Male % through the years:";
var cnt = Count(fs);
if (cnt > 0) {
// loop through results
for (var f in fs) {
var finalval = f["Value_Final"];
if (finalval == -1) {
// result += TextFormatting.NewLine + " - Data Not Available (" + f["State_Year"] + ")";
} else {
result += TextFormatting.NewLine + " - " + Round(f["Value_Final"], 1) + "% (" + f["State_Year"] + ")";
} }
}
return result;This will look like this which is not very helpful either:
So you can handle those cases differently like this (in case you have census tract with part data and part no data. In this case you will collect all the years without data and add that list to the result:
// read out the location ID
var geoid = $feature["geoid_1"];
// create a query with that ID
var sql = "geoid_1 = @geoid";
// filter the data using the query
var fs = OrderBy(Filter($layer, sql), "State_Year ASC");
// get the count, and only process if you have results
var nodatayears = [];
var result = "Male % through the years:";
var cnt = Count(fs);
if (cnt > 0) {
// loop through results
for (var f in fs) {
var finalval = f["Value_Final"];
if (finalval == -1) {
// result += TextFormatting.NewLine + " - Data Not Available (" + f["State_Year"] + ")";
nodatayears[Count(nodatayears)] = f["State_Year"];
} else {
result += TextFormatting.NewLine + " - " + Round(f["Value_Final"], 1) + "% (" + f["State_Year"] + ")";
} }
}
if (Count(nodatayears) > 0) {
result += TextFormatting.NewLine + " - Data Not Available for years: " + Concatenate(nodatayears, ", ");
}
return result;Which will look like this:
