Comparing an Aggr() function to a single value

[a_mullick]

Hi,

Something is not working with my expression but I can't see why...I need a fresh persepective please!

In the table below, I am calculating the average number of logons (and std. dev) per student on a course - I then calculate the avg minus 1 std. dev (Column(1) - Column(2)). What I want to do now is find out how many students have an average number of logons that fall below the threshold of this calculated value. As you can see, the expression (highlighted in red) returns 0; however in the next column you can see that I have the same expression and just hard-coded the value of 1.39 and it gives the correct expected results of 4.

Course Name	Mode of Attendance Group	Year / Level of Course Description	avg	std	Column(1) - Column(2)	=Sum(if( aggr(sum({$< [Log Year] = {2014} >} vol) / 20, [Student ID]) < column(3), 1, 0))	=Sum(if( aggr(sum({$< [Log Year] = {2014} >} vol) / 20, [Student ID]) < 1.39, 1, 0))
			2.64	1.25	1.39
BSC HONS BUSINESS	Full Time	1 - 1st Year	2.64	1.25	1.39	0	4

In case there was something 'odd' about the calculations for avg and std. dev I tried replacing them with hard-coded figures too, and still get the same results. So it seem to be pointing to how I reference 'Column(3)'...?

Any thoughts welcome...

Thanks,

Azam

[stephenedberkg]

column(1)-column(2)   change column name as Gap

and write the expression like

try this

=sum(if(aggr(sum({$<[log year]={2014}>}vol/20,[student ID]) ,< gap,1,0))

[a_mullick]

Hi,

Thanks, but no change to the results...

Azam

[stephenedberkg]

1.39 instead of use what you write the expression in column 3

or

=sum(if(aggr(sum({$<[log year]={2014}>} vol/20,[student ID]) < gap,1,0))

[a_mullick]

Hi,

If it helps, I've mocked up a test example that reproduces the 'issue'...(can't share the original...sensitive data etc..)

Thanks,

Azam

[jonathandienst]

You cannot include Column(3) in a Sum(). Replace "Column(3)" with the expression for column 3.

[simenkg · Accepted solution]

Change it to:

sum(aggr(if(sum(Vol) < avg(total Vol) - Stdev(total Vol), 1, 0), Student))

[a_mullick]

Thanks Simen... that has solved my problem.