Avg and count in an expression - help needed

[#1]

Hi, I am trying to create the following expression, average Scores where count of ResponseID is more than 5 and Year equal max(Year).  So far I have the following expression but it doesn't seem to work.  Any help would be much appreciated:

Avg({1<Year={$(=max(Year))},count(ResponseID)={">$(=5)"}>}Scores)

thanks

[swuehl]

Try

=Avg({1<Year={$(=max(Year))},Scores = {"=count(ResponseID)>5"}>} Scores)

or

=Avg({1<Year={$(=max(Year))},Scores = {"=count({<Year = {$(=max(Year))}>} ResponseID)>5"}>} Scores)

I assumed that you want to count the ResponseID based on Scores (..Scores where count ...), but maybe you need to use UserID or something like this instead of Scores in the set expression.

Regards,

Stefan

[#3]

unfortunately that didn't work, the following script does work, though I am just missing the count function for the responseID:

=Avg({$<resonseid={">5"}, Year={$(=max(Year))}>}MarsScores)

Any ideas?

[swuehl]

Many ideas...

What's the name of the entity/field you want to filter by the count(responseID)>5?

Like: by what dimension you want to group, using a count(responseID) as expression?

And why do you filter additionally by resonseid = {">5"}?

I think it would be really helpful if you could describe your datamodel / setting a bit closer.

Regards,

Stefan

[#5]

Hi Stefan,

thanks for getting back to me, the data I am using is some survey results data and my dimension is Applications and this is also what I will using for the grouping.

Each respondent to the survey scores each application they use with a score between 1-10.  I have multiple years of data but only want to show the latest year in this chart. If a location has 5 or less people then we ignore them from the results and hence why I need the count function for resonseid = {">5"}.  This will then allow me to display the average satisfaction for all applications that are used.

Let me know if you need some more information

thanks

Ed

[swuehl]

If you use resonseid = {">5"}

then this will filter resonseid values that are greater than 5. This will not filter a count of records per application, right?

I created some sample data:

LOAD

(2010+floor(RAND()*3)) as Year,

chr(65+floor(RAND()*10)) as Application,

ceil(RAND()*10) as Score

AutoGenerate 100;

Then used a table chart with dimension Application and as expression:

=avg({<Year={$(=max(Year))}, Application={"=count({<Year={$(=max(Year))}>} Score)>5"}>} Score)

Is this what you are looking for?

If not, please post some lines of sample data together with your expected outcome.

Regards,

Stefan