Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Sum a value with duplicate data

I have data that looks something like this:

UserIDCourse NameOther Value
1

Course 1

10
1Course 14
1Course 110
1Course 210
1Course 24
1Course 210

The data is like this due to me joining two tables together - the "Course Name" and "Other Value" have nothing in common other than that they apply to the same user.

What I need to do is sum the "Other Value" so I get 24 for the user, regardless of the number of courses they have. I think I have to use some sort of aggregate function, I've played around with aggr but haven't had any luck.

Any help would be appreciated!!

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Sum a value with duplicate data

I think it would be better to not join the tables, but keep your OtherValue fact values linked by UserID.

If you don't want to do it, maybe

=Sum(OtherValue) / Count(DISTINCT [Course Name])

View solution in original post

2 Replies
Highlighted
MVP
MVP

Re: Sum a value with duplicate data

I think it would be better to not join the tables, but keep your OtherValue fact values linked by UserID.

If you don't want to do it, maybe

=Sum(OtherValue) / Count(DISTINCT [Course Name])

View solution in original post

Specialist II
Specialist II

Re: Sum a value with duplicate data

Not sure what you want the end result to look like.  Are you looking for something like this?

UserIDCourse NameOther Value
1Course 124
1Course 224

It would essentially be an average.  If a user has a 3rd course with different total it could end up being not what you're expecting.

But to do this you could do.

aggr(nodistinct (Sum(OtherValue) / Count(DISTINCT [Course Name])),UserID)