Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
juriengroot
Contributor III
Contributor III

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!

11 Replies
rajiv_maskara
Contributor II
Contributor II

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

sunny_talwar

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

sunny_talwar

Here you are (attached a sample)

Capture.PNG

rupamjyotidas
Specialist
Specialist

Dimension: Order ID

Equation:

A: Sum(DISTINCT [Value A])

B: Sum([Value B])

juriengroot
Contributor III
Contributor III
Author

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.example.PNG

sunny_talwar

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))

lakshmikandh
Specialist II
Specialist II

juriengroot
Contributor III
Contributor III
Author

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 ).  

juriengroot
Contributor III
Contributor III
Author

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.