Average($ListOfValues) doesn't work?

[HazMatt]

I am writing a script where I need to calculate the average of a bunch of numbers.  There may be 1 number (where no averaging is calculated) or up to 10 or so.  There may be gaps in the numbers from time to time as well.

 

Set Variable [$CalculatedAvg; Value:Average($ListOfValues)]

Doesn't work—no variable is created.

 

Can anyone suggest something that could work?  I was hoping to use the Average function because it automatically accounts for blank values.  Not looking forward to having to eliminate blank values, adding up the remaining values and dividing by ValueCount().  Perhaps virtual list with an average summary field?

 

I guess I'm just surprised some of these aggregate functions don't work on a list of values in a variable.

 

I ran some tests in the Data Viewer, and this is the error message I get:

In the function Average, Count, Extend, GetRepetition, Max, Min, NPV, StDev, Sum, GetSummary or GetNthRecord, an expression was found where a field alone is needed.

[eos]

I guess I'm just surprised some of these aggregate functions don't work on a list of values in a variable.

 

They don't work on lists period.

 

Try

Case (
  IsEmpty ( $myList ) ; "" ;
  ValueCount ( $myList ) = 1 ; $myList ;
  Evaluate ( "Average (" & Substitute ( $myList ; ¶ ; " ; " ) & ")" )
)

[comment]

I am writing a script where I need to calculate the average of a bunch of numbers.  There may be 1 number (where no averaging is calculated) or up to 10 or so.

 

Where do these numbers come from? Normally, "a bunch of numbers" would be stored in "a bunch of records" - which can be averaged easily.

 

There may be gaps in the numbers from time to time as well.

 

Not sure what you mean by that.

 

 

 

---

Edit:

 

I am guessing, from what you said later, that gaps mean blank values? If so, you could average your list using something like =

Let ( [
prep = Substitute ( TrimAll ( Substitute ( $yourListOfNumbers ;  ¶ ; " " ) ; 0 ; 0 ) ;  " " ; ", ")
] ;
Case (
WordCount ( prep ) ≤ 1 ; prep ;
Evaluate ( "Average ( " & prep & " )" )
)
)

Note, however, that this is not much more than a hack. A better solution to the problem would eliminate it from the start.

Edited October 10, 2014 by comment

[David Jondreau]

I guess I'm just surprised some of these aggregate functions don't work on a list of values in a variable.

First line of the help entry for Average(): "Returns a value that is the average of all valid, non-blank values in field."

[HazMatt]

First line of the help entry for Average(): "Returns a value that is the average of all valid, non-blank values in field."

 

Yep, I know.  Just surprised, is all.

 

Yes, the "gaps" I speak of are null values.  Average() automatically ignores blanks, so it would have been nice to use it.

 

eos and comment, I spent the time since my original post to try and figure out an alternative, and I came up with a similar method, although it looks like your script steps are probably more error proof.  Here's my custom function, GetAverage(Values):

/*

Purpose
Calculates the average of a list of values.  Useful for when the Average() function does not work.

With inspiration from:
• Debi Fuchs' "CullNulls" (http://www.briandunning.com/cf/897) custom function
• Tom Fitch's "ListSum" (http://www.briandunning.com/cf/1309) custom function

History
2014/10/10 - Matt Larson (Cimbura.com) - Custom function creation

*/

Let(

[
	str = "#||#*howdy*#||#";  //  Unique string used to strip out null values
	v = Substitute(str & Values & str; ["¶¶"; ¶ & str & ¶]; ["¶¶"; ¶ & str & ¶]; [str & ¶; ""]; [¶ & str; ""]; [str; ""]);  //  Strip out unwanted carriage returns
	v = Substitute(v; "¶"; ";")  //  Convert carriage returns to semicolons for use in Average() function
];

Evaluate("Average(" & v & ")")

)

I was pretty vague with "bunch of numbers", wasn't I?    Sorry.  They are actually the results of an ExecuteSQL script step, so I wanted to avoid having to needlessly navigate to a different layout, set up additional summary fields, etc.

 

Thanks a lot guys.

[eos]

I was pretty vague with "bunch of numbers", wasn't I?    Sorry.  They are actually the results of an ExecuteSQL script step,

 

Then you could use SQL's AVG() (which also ignores NULL values).

 

Alternatively, use an SQL query that in the WHERE clause includes the predicate " … theNumberField IS NOT NULL".

[HazMatt]

Ahh, advanced SQL coding techniques…  Probably beyond my ability (and time) at this point.

 

I just updated my skill level in my profile to "Expert" but I think you are ahead of me.  

[David Jondreau]

v = Substitute(str & Values & str; ["¶¶"; ¶ & str & ¶]; ["¶¶"; ¶ & str & ¶]; [str & ¶; ""]; [¶ & str; ""]; [str; ""]); // Strip out unwanted carriage returns

 

Interesting technique...One issue though is it breaks if there's a leading Null value.

 

I'd modify it to something I learned from comment:

 

Substitute ( TrimAll (  Substitute ( Values ; [ "¶" ; " " ] ) ; 0 ; 0 ) ; " " ; ";" )

[eos]

Ahh, advanced SQL coding techniques…  Probably beyond my ability (and time) at this point.

 

Not really advanced … and it should require a lot less time and effort to use

 

ExecuteSQL ( " SELECT AVG ( myNumberField ) FROM myTable " ; "" ; "" )

 

than cleaning up a list in order to Evaluate() it – all of which eventually amounts, as comment noted quite correctly, to just a hack.

 

 

I just updated my skill level in my profile to "Expert"

 

I guess everybody's an expert in something …   as can be observed here