topic Re: Comparing an Aggr() function to a single value in QlikView

Comparing an Aggr() function to a single value

a_mullick — Fri, 25 Sep 2015 09:21:03 GMT

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

Re: Comparing an Aggr() function to a single value

stephenedberkg — Fri, 25 Sep 2015 09:34:25 GMT

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

Re: Comparing an Aggr() function to a single value

a_mullick — Fri, 25 Sep 2015 09:40:23 GMT

Hi,

Thanks, but no change to the results...

Azam

Re: Comparing an Aggr() function to a single value

stephenedberkg — Fri, 25 Sep 2015 09:47:00 GMT

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

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

Re: Comparing an Aggr() function to a single value

a_mullick — Fri, 25 Sep 2015 09:59:19 GMT

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

Re: Comparing an Aggr() function to a single value

jonathandienst — Fri, 25 Sep 2015 10:02:34 GMT

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

Re: Comparing an Aggr() function to a single value

simenkg — Fri, 25 Sep 2015 10:04:46 GMT

Change it to:

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

Re: Comparing an Aggr() function to a single value

a_mullick — Fri, 25 Sep 2015 10:25:34 GMT

Thanks Simen... that has solved my problem.