Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

TOTAL does not work within AGGR?

Has anyone else had issues where total does not work within the Aggr function or have any ideas for a work around?

 

For example, Sum(Aggr(Avg(Total Amount) does not actually show the total amount.

Labels (2)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Are you looking for this?

Capture.PNG

View solution in original post

8 Replies
Highlighted

Can you post full expression?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator III
Creator III

Sorry, I got sidetracked. I'm using Aggr because the data can have multiple records per ID.

Sum(Aggr(Avg(Total Amount), ID))

 

Highlighted

Thank you !!

Can we have an example where it fails for you, I am not seeing anything wrong in that?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted

This expression will not give the the total

It will calculate the average amount for all IDs and then sum that (ie multiply by) the number of IDs. Try the expression without the TOTAL keyword.

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Creator III
Creator III

Yes, I know what the aggr function is doing. I need it because of the data structure. The issue is I need to calculate % of totals. So ideally I'd have the formula without TOTAL to get the amount per dimension and then the Formula with TOTAL unless I'm misunderstanding how total works.

This is how it looks:

IDID2Amount
A1100
A2100
A3100
B430
C510
D650
D750

 

From what I understand, Aggr would give me this:

IDAmount
A100
B30
C10
D50

 

I need to calculate % of total to get this:

 

ID1ID1Amount%
A10053%
B3016%
C105%
D5026%
Highlighted
MVP
MVP

Are you looking for this?

Capture.PNG

View solution in original post

Highlighted

Try this?

Num((Avg(Amount)/Sum(TOTAL Aggr(Avg(Amount), ID))),'#,##0%')

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator III
Creator III

So far so good. I didn't even think to move TOTAL to the outside sum function and was hung up trying other things for hours yesterday. Thank you!