topic Sum a value with duplicate data in App Development

Sum a value with duplicate data

Anonymous — Thu, 19 Jan 2017 16:50:42 GMT

I have data that looks something like this:

UserID	Course Name	Other Value
1	Course 1	10
1	Course 1	4
1	Course 1	10
1	Course 2	10
1	Course 2	4
1	Course 2	10

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

Re: Sum a value with duplicate data

swuehl — Thu, 19 Jan 2017 16:54:39 GMT

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

Re: Sum a value with duplicate data

Anonymous — Thu, 19 Jan 2017 17:49:30 GMT

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

UserID	Course Name	Other Value
1	Course 1	24
1	Course 2	24

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)