How Aggr() handles selection with set modifier

[#1]

Hi, I am trying to understand the behaviour or current selections on Aggr(). For example I have the following data set:

LOAD * INLINE [

Test, Target, SessionNum, Student

A, 10, 1, John

A, 10, 2, Mark

A, 10, 3, Mark

B, 58, 1, John

B, 58, 2, Kelly

C, 123, 1, Mark

D, 77, 1, Mark

D, 77, 2, John

D, 77, 3, John

D, 77, 4, Kelly

D, 77, 5, John

E, 947, 1, John

E, 947, 2, Kelly

F, 3, 1, Kelly

];

Then I created a straight table with the following:

Dimension: [Student]

Expression: Sum({1} Aggr(Avg([Target]), [Test]))

The result is:

John | 1015

Kelly | 3

Mark | 200

I can understand how these numbers come about. But when I select just one Student, "Mark", the result becomes this:

John | 10

Mark | 200

And I can't quite understand why this is so. Is it that QlikView will first look at what Tests that are filtered according to my Student selection (A, C, and D), and then go back to the original data set and pick only these Tests and pass them to the Aggr() function?

Thanks in advance!

[sunny_talwar]

If you are looking to ignore selection when you select a student (or any other field)... you need to use {1} in your inner aggregation also

Sum({1} Aggr(Avg({1}[Target]), [Test]))

[#3]

Thanks. But I'd like to also understand why the expression behaves this way... so do you mean the Aggr() will honour the selected set first before constructing the virtual table?

Thanks again.

[vinieme12]

Do you know how nested SQL statements work?

     The inner most query is executed first and the results passed on to the one encapsulating it

Similarly the innermost expression executes first and the results are passed to the expression encapsulating it

so if your inner expressions is set to {$}  then the outer {1} will do nothing because your inner expression is still affected by selections

Does that make sense?

[sunny_talwar]

Think about this way that you have created a straight table with Test as dimension and Avg(Target) as expression... when you select something, would you see the selections making impact? You will... right? Now this is the same exact virtual table you are creating and Avg(Target) will filter out non-selected items and the final sum will be based on that. In order to ignore all selections, you need to ignore selections in inner as well as outer aggregations

Sum({1} Aggr(Avg({1}Target), Test))

[#6]

Hi, thanks again for your advice. I now understand the virtual table resulting from the Aggr() should be something like this after selecting "Mark"

Test | Avg(Target)

A | 10
C | 123
D | 77

But when it's passed to the outer expression, why is John only 10, not 10+77 (since John also appears in Test D)? and Mark is only 200, not 10+123+77 (since Mark also appears in Test A)?

Actually I wasn't trying to achieve a particular expression - I was just trying to see how set analysis, TOTAL, and aggr() all come to together...

Thanks again!

[#7]

Thanks for your advice. I guess I'm just a bit confused on how selections and set analysis work with aggregations. But your explanation clears up a few things for me already. Thanks again!

[tresesco]

I guess that NODISTINCT would help you decode the behavior. Aggr() by default returns the distinct output against every possibly combination of output.

Hope this helps.

[#9]

Thanks. By "distinct output against every possibly combination of output", do you mean the resulting virtual table actually includes all the dimensions, not just Test as its only dimension? Because my assumption was that Aggr() is like a SQL GroupBy, so in this case the virtual table should only produce Test as its dimension, and the value is the Aggr() values...

[sunny_talwar · Accepted solution]

Is this what you are hoping to achieve?

Sum({1} Aggr(Avg({1}[Target]), [Test], Student))