topic How Aggr() handles selection with set modifier in QlikView

How Aggr() handles selection with set modifier

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: How Aggr() handles selection with set modifier

sunny_talwar — Sat, 26 Aug 2017 23:37:56 GMT

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

Re: How Aggr() handles selection with set modifier

Sun, 27 Aug 2017 01:19:00 GMT

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.

Re: How Aggr() handles selection with set modifier

vinieme12 — Sun, 27 Aug 2017 05:13:31 GMT

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?

Re: How Aggr() handles selection with set modifier

sunny_talwar — Mon, 28 Aug 2017 01:07:43 GMT

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

Re: How Aggr() handles selection with set modifier

Mon, 28 Aug 2017 06:22:17 GMT

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!

Re: How Aggr() handles selection with set modifier

Mon, 28 Aug 2017 06:24:37 GMT

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!

Re: How Aggr() handles selection with set modifier

tresB — Mon, 28 Aug 2017 07:09:50 GMT

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.

Re: How Aggr() handles selection with set modifier

Mon, 28 Aug 2017 09:39:20 GMT

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

Re: How Aggr() handles selection with set modifier

sunny_talwar — Mon, 28 Aug 2017 11:27:48 GMT

Is this what you are hoping to achieve?

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

Re: How Aggr() handles selection with set modifier

Fri, 01 Sep 2017 07:36:23 GMT

Thanks this is helpful. I shall explore more to try to get a better understanding.