topic Aggregation along a selected dimension in QlikView

Aggregation along a selected dimension

Thu, 20 Mar 2014 14:50:26 GMT

Stupid question, I am sure...

I'd like to understand how I can sum the Bill amount (see table below) by selecting the dimension along which I'd like to aggregate.

In plain english, this would be: "sum the Billing amount by distinct Order ID (to avoid summing up every rows"

Expected answer = 100 + 200 + 400 = 700

Who can help me get the right syntax?

Order ID	Item name	Item QTY	Item price	Bill amount
B1	Item A	1	20	100
B1	Item B	2	40	100
B1	Item C	2	40	100
B2	Item A	2	40	200
B2	Item D	1	160	200
B3	Item A	1	20	400
B3	Item B	2	40	400
B3	Item E	4	340	400

Re: Aggregation along a selected dimension

Nicole-Smith — Thu, 20 Mar 2014 14:53:35 GMT

sum(aggr(only([Bill amount]),[Order ID]))

Re: Aggregation along a selected dimension

Thu, 20 Mar 2014 14:57:28 GMT

Hi,

you can use sum(DISTINCT Bill Amount)

Re: Aggregation along a selected dimension

Thu, 20 Mar 2014 14:59:06 GMT

Hey Jean,

I this the final view of the table? Can you post an example of how the final table will look like. What all dimensions and expressions if different from this. Or do you want this 700 in a text box?

You can try sum(aggr(sum(distinct Bill amount),OrderID))

This will work in Text Box perfectly but in table it will cause nulls for everything bur first Order ID but total will be correct.

It depends on where you want the result.

Re: Aggregation along a selected dimension

Nicole-Smith — Thu, 20 Mar 2014 14:59:11 GMT

Torsten Rehder's solution will only work if two Order ID's never have the same Bill Amount.