11 Replies Latest reply: Dec 5, 2016 6:04 AM by Sunny Talwar

# Sum of aggregate by dimension

This is my source data:

Order IDValue AValue B
A26
B54
B52
C15
C15
C14
D68

I want my result table in Qlik Sense to be:

Order IDDistinct Sum Value ASUM Value B
A26
B56
C114
D68
Total1434

I tried everything with Aggr, Distinct and Sum, but haven't succeeded yet. Who can point me in the right direction? I would be very grateful!

• ###### Re: Sum of aggregate by dimension

Hi,

It looks like you have done a left join in the script on OrderID and because there are multiple values of B for each orderID, A values have been duplicated. It would be best not to join the two levels and let Qlik show the correct sum.

Alternately, if you have not joined tables in the script, I would take care of this in the script editor and create two tables from this single table. Table1: Should have OrderID and FirstValue(ValueA) and group by OrderID. Table 2: As is table without ValueA. Now the frontend table would show as desired

• ###### Re: Sum of aggregate by dimension

Yeah unfortunately I have to join, because of other implications in my data model. Might need to do some concessions though and keep them seperated.

• ###### Re: Sum of aggregate by dimension

Try this for Value A

Sum(Aggr(Only([Value A]), [Order ID]))

or

Sum(Aggr(Min([Value A]), [Order ID]))

or

Sum(Aggr(Max([Value A]), [Order ID]))

or

Sum(Aggr(Avg([Value A]), [Order ID]))

or

Sum(Aggr(Sum(DISTINCT [Value A]), [Order ID]))

For Value B just Sum([Value B]) should work

• ###### Re: Sum of aggregate by dimension

Here you are (attached a sample)

• ###### Re: Sum of aggregate by dimension

Thanks, but the problem happens when I introduce another dimension, it will show 0 for the next rows, while I want it to show the Value of A. I only want it to affect the total.

• ###### Re: Sum of aggregate by dimension

Not sure what you are expecting to see, but may be you need to add your second dimension to the Aggr() function

Sum(Aggr(Max([Value A]), [Order ID], ExtraDimension))

• ###### Re: Sum of aggregate by dimension

I want 19 as a total result in the example above, but I want it to show it's value of A on each line. When I add the Extra dimension to the calculation, the total gets distorted..
I am afraid it's not possible what I want with the aggr. formula. Splitting up the tables gives the correct result, but it has implications for the rest of my datamodel. (another story ).

• ###### Re: Sum of aggregate by dimension

Dimension: Order ID

Equation:

A: Sum(DISTINCT [Value A])

B: Sum([Value B])

• ###### Re: Sum of aggregate by dimension

Thanks, unfortunately in the complete dataset, values are not always unique. See above my comments to Sunny for more details.