Using AGGR in list boxes

pauljohansson — Fri, 04 Oct 2013 20:25:03 GMT

Hi,

Do you have a good understandning of the AGGR function and listboxes. Continue to read

Ok, lets start with a clear description of the case.

My table structure:

Date | TransType | Amount | Customer

The interface: I have two list boxes using AGGR expressions:

Listbox1

=aggr(sum(IF(TransType = 'Deposit', Amount)), Customer)

Listbox2

=aggr(sum(IF(TransType = 'Withdrawal', Amount)), Customer)

When i make the selection '100' in Listbox1 I get all the customers that have a deposited the value equal to 100, fine. And of course the same goes when I select 100 in Listbox2.

However, i want to be able to select all the customers that have Deposited exactly 100 OR Withdrawl exactly 100.

If I select 100 in both of the listboxes, I get all the customers that both have deposited 100 AND withdrawald 100.

But want im looking for is this:

Customer | Deposit | Withdrawl

a 100 0

b 100 0

c 100 0

d 0 100

e 0 100

f 0 100

One might think that the solution is to create a listbox based on the following expression:

=aggr(sum(IF(TransType = 'Deposit' or TransType = 'Withdrawal', Amount)), Customer)

This gets close, but its not right, because it leaves us with a result that might look like:

Customer | Deposit | Withdrawl

a 100 0

b 100 0

c 50 50

d 30 70

e 0 100

f 0 100

Do anyone have any input how to solve this? Or maybe can point in a direction?

Thanks in advance,

Paul

Re: Using AGGR in list boxes

stevedark — Fri, 04 Oct 2013 23:07:31 GMT

Hi Paul,

If you add the TransType to the aggr parameters does that give the desired result?

=aggr(sum(IF(TransType = 'Deposit' or TransType = 'Withdrawal', Amount)), Customer, TransType)

Personally, I would look at doing the sum in the load script, as it will then perform a lot better at run time - and only cause a small hit at load time. After loading your main table you could load the aggregate amounts with a resident load like this:

Totals:

LOAD

Customer,

TransType,

sum(Amount) as [Total Amount]

RESIDENT OtherTable

GROUP BY

Customer,

TransType

;

This way you can then just have a simple list box on the Total Amount field, and this would then make the selection in the way that you require.

As a rule the more you can do in the load script the better.

Hope that helps,

Steve

http://www.quickintelligence.co.uk/

topic Using AGGR in list boxes in QlikView

Using AGGR in list boxes

Re: Using AGGR in list boxes