Jump to content
Sign in to follow this  
HazMatt

Average($ListOfValues) doesn't work?

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.

Share this post


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

Share this post


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

Share this post


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.

Share this post


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

Share this post


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:

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:
    Bob Minteer 
×
×
  • Create New...

Important Information

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