Jump to content

Average($ListOfValues) doesn't work?


This topic is 2123 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.
Link to post
Share on other sites

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 ; ¶ ; " ; " ) & ")" )
)
Link to post
Share on other sites

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 by comment
Link to post
Share on other sites

 

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.

Link to post
Share on other sites

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".

  • Like 1
Link to post
Share on other sites

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.   :worship:

Link to post
Share on other sites

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 ) ; " " ; ";" )

Link to post
Share on other sites

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 …  :D as can be observed here

Link to post
Share on other sites

This topic is 2123 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.