# 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
Did you mean:
Highlighted
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)

• ### Total

1 Solution

Accepted Solutions
Highlighted
MVP

Are you looking for this?

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

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

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:

 ID ID2 Amount A 1 100 A 2 100 A 3 100 B 4 30 C 5 10 D 6 50 D 7 50

From what I understand, Aggr would give me this:

 ID Amount A 100 B 30 C 10 D 50

I need to calculate % of total to get this:

 ID1 ID1Amount % A 100 53% B 30 16% C 10 5% D 50 26%
Highlighted
MVP

Are you looking for this?

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

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!