topic Re: Aggregate function in Set Analysis with and without $ Expansion in QlikView

Aggregate function in Set Analysis with and without $ Expansion

kamal_sanguri — Tue, 14 Jun 2016 15:03:51 GMT

Hi,

I was trying using Min/Max aggregate function in Set Analysis. I used function in different way and got different result.

LOAD SCRIPT

**********************************************************************************************************************************************

Data:

Load *

Inline

[

Marks, Name

150, A

250, B

120, C

320, D

188, E

235, F

354, G

199, H

297, I

];

NewData:

Load

Max(Marks) as MaxMarks

Resident Data;

Let MaxMarks = Peek('MaxMarks',0,'NewData');

Drop Table NewData;

**********************************************************************************************************************************************

Function : =Concat({<Marks={"$(=Min(Marks))"}>}Name,';')

Output: C

Function : =Concat({<Marks={"=Min(Marks)"}>}Name,';')

Output: A;B;C;D;E;F;G;H;I

It seems, in second case.. it is going row by row but I think, it should not be the case when use set..

Please share your thoughts..!!

Thanks,

-Kamal

Re: Aggregate function in Set Analysis with and without $ Expansion

rubenmarin — Tue, 14 Jun 2016 15:10:27 GMT

At first sight it seems that set analysis isn't working in your second function, so it returns the values it will return removing the set analysis. Can you try it?

Re: Aggregate function in Set Analysis with and without $ Expansion

sunny_talwar — Tue, 14 Jun 2016 15:12:03 GMT

I think your second expression's set analysis is not working at all. Without Dollar sign expansion of the Min() function, it is not getting evaluated to any value. That is why you are seeing all the values from your Name field. Where as the first one is evaluating the Min() function and only showing Marks where it equals Min(Marks)

Re: Aggregate function in Set Analysis with and without $ Expansion

Colin-Albert — Tue, 14 Jun 2016 15:13:20 GMT

Create straight table chart with Name as a dimension and your two expressions and you can see what is happening.

Re: Aggregate function in Set Analysis with and without $ Expansion

sunny_talwar — Tue, 14 Jun 2016 15:13:20 GMT

Actually, at another look, it is working, but it is working like a search string on Marks field. And you are right search strings kind of work line by line on the field they are getting evaluated on.

Re: Aggregate function in Set Analysis with and without $ Expansion

swuehl — Tue, 14 Jun 2016 15:14:32 GMT

The second expression is working as designed.

The advanced search is creating a hyper cube with marks as dimension and

=Min(Marks)

as expression, which essentially return the dimensional value, a number, which then would be interpreted as TRUE.

All true dimensional values will be included in the set.

Re: Aggregate function in Set Analysis with and without $ Expansion

marcus_sommer — Tue, 14 Jun 2016 15:14:45 GMT

The reason is that the $-sign expansion $(=Min(Marks)) worked like a variable and which will be calculated once and at first on a global level before the chart is calculated. This meant you couldn't use a $-sign expansion if you need the expression to be calculated on row-level.

- Marcus

Re: Aggregate function in Set Analysis with and without $ Expansion

rubenmarin — Tue, 14 Jun 2016 15:21:55 GMT

Thanks for explanation swuehl, I couldn't guess why is returning all the values

Re: Aggregate function in Set Analysis with and without $ Expansion

kamal_sanguri — Wed, 15 Jun 2016 06:11:48 GMT

Thanks much to all of you for your suggestions..

So,does that mean.. we should use $ sign expansion while using a function in set.. otherwise we may get unwanted results..?

Thanks,

-Kamal

Re: Aggregate function in Set Analysis with and without $ Expansion

marcus_sommer — Wed, 15 Jun 2016 06:51:55 GMT

This couldn't be answered in general - it will be always depend on the datamodel and which views with which conditions should be displayed. But if you knows that there are differences between various syntaxes you could it change fast from one to another when the results aren't like expected.

- Marcus

Re: Aggregate function in Set Analysis with and without $ Expansion

kamal_sanguri — Wed, 15 Jun 2016 07:03:07 GMT

Thanks for quick response, the reason why I asked this was.. if I want to calculate row by row and want to have all the items concatenated.. I can easily use..

=Concat(Name,' ;')