4 Replies Latest reply on Oct 5, 2017 1:13 PM by . Berrett.Rice

Sum across unique records only - probably LOD?

I have a list of courses that repeats. I need to know how many times a student has been absent in all of their courses combined - but the courses repeat so I need to only add the absences across one instance of the courses. For example for Mary I might have two columns in the source data: Course | Absences - and they might read:

Math | 1

Science | 0

PE | 1

PE | 1

Science | 0

Math | 1

PE | 1

I would want a Sum of 1 + 2 + 0 + 1 = 4 because I'm only adding one instance of each class name, ignoring the repeated records. I am not very familiar with LODs (I have a scheduled in Vegas next week) and I wonder if that's the way to do this. Or maybe there's an easier way?

• 1. Re: Sum across unique records only - probably LOD?

can you try this?

sum({fixed [student]:min([absentdays])})

• 2. Re: Sum across unique records only - probably LOD?

Hi

yes you are probably looking at an lod like a Fixed statement - the statement will take the dimensions that preceed the colon : and create permutaions so you lod could take the form  {fixed [student],[course]: if sum([absence] > 0 then 1 else 0 end}

The when you create the VIZ you would want to sum the fixed statement

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 3. Re: Sum across unique records only - probably LOD?

Try This:

{FIXED Student: SUM({FIXED  Student, Course:AVG(Absence)})}

Thanks

Deepak

• 4. Re: Sum across unique records only - probably LOD?

That did it. Thanks so much Deepak!