Listbox with Aggr() and additional expressions not working as expected

[nafi_qlikview]

Hi,

I believe this issue has been discussed in one form or another before, but I couldn't find a solution to my particular problem. I've also read up on how Qlikview deals with NULLs/missing values, but can't see why this would be causing what I'm seeing.

Instead of a straight dimension, I'm using an Aggr() expression as dimension in a listbox to be able to filter down the shown dimension values. In addtion, I show other information for each of the resulting dimension values via the expression tab.

Problem is, that the expressions displayed and the sort order applied isn't what it should be.

Here's an example (Since I couldn't manage to include screenshots inline, I'll enter the values in tables):

Input data:

LOAD *

INLINE [

  ID, Name

  1 , 'Test1'

  2 , 'Test2'

  3 , 'Test3'

  4 , 'Test4'

];

LOAD *

INLINE [

  ID, Filter, Value

  1 , 1     , 1

  2 , 1     , 1

  1 , 2     , 1

  3 , 2     , 1

];

The dimension in the first listbox is 'Aggr(Only(Name), Name)' (the Only() will contain filters in the real app, but they aren't needed for demonstration of the issue; in fact even 'Aggr(Name, Name)' behaves the same way). For demonstration puposes, I've also created a second listbox with the straight dimension 'Name', which behaves as expected. Both are ordered by the first expression column, descending.

Aggr(Only(Name), Name)	Sum({<Filter={1}>} Value)	Sum({<Filter={2}>} Value)	Sum(Value)
Test4	0	0	0
Test3	0	1	1
Test2	1	0	1
Test1	1	1	2

Name	Sum({<Filter={1}>} Value)	Sum({<Filter={2}>} Value)	Sum(Value)
Test1	1	1	2
Test2	1	0	1
Test3	0	1	1
Test4	0	0	0

As you can see, the sort order is already off in here. If I now also disable the last expression, I get the following:

Aggr(Only(Name), Name)	Sum({<Filter={1}>} Value)	Sum({<Filter={2}>} Value)
Test4
Test3	0	1
Test2	1	0
Test1	1	1

Name	Sum({<Filter={1}>} Value)	Sum({<Filter={2}>} Value)
Test1	1	1
Test2	1	0
Test3	0	1
Test4	0	0

Now the line for Test4 is completely empty in the first listbox, but it's still shown. Note: It's not displaying '-' to indicate that it's NULL.

Can anyone please explain why Aggr() messes the whole thing up?

Thanks,

Martin

[stigchel · Accepted solution]

My guess is that this is because calculated dimensions are evaluated differently. See e.g.

https://community.qlik.com/blogs/qlikviewdesignblog/2013/08/06/it-s-all-aggregations

If you use the same aggregation in your expressions,

Aggr(Sum({<Filter={1}>} Value),Name)

The results and sorting will be what you expect. Should you want to display 0 instead of missing you can wrap it in a sum expression, so

Sum(Aggr(Sum({<Filter={1}>} Value),Name))

[nafi_qlikview]

Awesome, it works! And the Sum() tip is very useful.

Thanks you so much, I've already spent quite a while looking into this!